1

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

BenLa
  • 27
  • 1
  • 6

2 Answers2

0
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.

Tarik
  • 161
  • 4
  • Thanks! The only problem with your solution is that the keys are dynamic, I don't want to implicitly refer to specific key but have a generic extraction that loops on all tags. – BenLa Feb 13 '23 at 13:50
0

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;
Ajax1234
  • 69,937
  • 8
  • 61
  • 102