CODEWITHSUNDEEP
mysqlsqljsonmysql-jsonjson-extract
BenLa
BenLa

Reputation: 37

Convert json column content key value pairs to separated key value

My table looks like this:

id data
1 {tags: {"env": "dev", "owner": "me"}}

I want to fetch the data and inside the select query convert the data column to the following format:

id data
1 {tags: [{"key": "env", "value": "dev"}, {"key": "owner", "value": "me"}]}

I've tried several JSON mysql functions but the closest I got is :

id data
1 {tags: [{"key": "env", "value": ["dev", "me"]}, {"key": "owner", "value": ["dev", "me"]}]}

Any suggestions?

Thanks

Upvotes: 1

Views: 682

Answers (2)

Ajax1234
Ajax1234

Reputation: 71471

This is a generic approach which will also work on data fields that have multiple top-level keys and multiple second-level keys:

select t1.id, (select json_objectagg(t1.k1, 
    (select json_arrayagg(json_object('key', t2.k2, 
         'value', json_extract(t.data, concat('$.', t1.k1, '.', t2.k2)))) 
       from json_table(json_keys(json_extract(t.data, concat('$.', t1.k1))), '$[*]' columns (k2 text path '$')) t2)) 
  from json_table(json_keys(t.data), '$[*]' columns (k1 text path '$')) t1) 
from tbl t;

Upvotes: 0

Tarik
Tarik

Reputation: 169

SELECT id, JSON_OBJECT("tags", JSON_ARRAY( JSON_OBJECT("key", "env", "value", JSON_EXTRACT(json_column, "$.tags.env")), JSON_OBJECT("key", "owner", "value", JSON_EXTRACT(json_column, "$.tags.owner")) )) as data FROM table_name

JSON_EXTRACT : extract the values of the "env" and "owner" keys from the json_column JSON_OBJECT : create two JSON objects with the "key" and "value" keys and the extracted values JSON_ARRAY : create a JSON array of these two objects and finally wraps the array in another JSON_OBJECT with the "tags" key.

Upvotes: 0

Related Questions