How can I set all JSON keys as column name in MYSQL select

Question

In mysql table one of the column is json, I want to select JSON field and set as column name

Json field value for class 3

{"name": "xyz 1", "email": "[email protected]"}
{"name": "xyz 2", "email": "[email protected]"}

Class 4

{"first_name": "abc1", "last_name": "pqr1"}
{"first_name": "abc2", "email": "pqr2"}

I can set specific key as a column name like

select json_extract(my_tags,'$.name') as name from {table_name} where class = 3;

But I want all key values as column name in select result. The key values will be different each time From the above example results column should be as follows name, email

select {my_tags} as name from {table_name} where class = 4;

But for class 4, needs column name should be first_name and last_name

Answer 1

You can't do that directly in MySQL. You could do something like this in a combination of MySQL and PHP. Query:

SELECT JSON_KEYS(my_tags) AS tags, JSON_EXTRACT(my_tags, '$.*') AS values FROM {table_name}

Then in PHP:

while ($row = $result->fetch()) {
    $tags = json_decode($row['tags']);
    $values = json_decode($row['values']);
    $data = array_combine($tags, $values);
    // process
}

This will give you an associative array which is the same as you would have got from the separate column queries you propose. You could array_merge $data into $row if that made processing easier.

Answer 2

You can write query like :

select 
  json_extract(my_tags,'$.name') as name
  json_extract(my_tags,'$.email') as email
from table_name;

So your result will be :

name email
xyz  [email protected]

Answer 3

you can get idea from below example

select  * from json_table(json_keys('{"shop_name":"National Chemist","shop_address":"dfsf","shop_email":"abc@gm","shop_mobile":"4545","shop_license":"4543545","invoice_bill_format":"a4_gst_logo","invoice_terms":"sdfdfds","invoice_popup":false,"sms_pack":false,"sms_tempalte":"dfdsff"}'
),'$[*]' COLUMNS( name varchar(20) PATH '$'))
as t

it will give you output like this

shop_name
shop_address
email
....