Question 20842 in Mysql Question posted in
The official MySQL documentation can be found here.

Convert json column content key value pairs to separated key value – Mysql

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

Tags:

2

Answers


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

    Login or Signup to reply.
  2. 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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search