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
2
Answers
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.
This is a generic approach which will also work on
data
fields that have multiple top-level keys and multiple second-level keys: