I have a table with 1 column and rows like –
Row- {'category': ['mobiles'], 'specs': ['4g', '2gb ram']}
Row- {'category': ['computer'], 'specs': ['2gb ram']}
I want to flatten the data into 2 columns-
category, mobiles
specs, 4g
specs, 2gb ram
category, computer
specs, 2gb ram
I tried this
SELECT `key`,`value`
FROM table_name
CROSS JOIN JSON_TABLE(
column_name,
'$.*' COLUMNS (
`key` TEXT PATH '$',
NESTED PATH '$[*]' COLUMNS (
`value` VARCHAR(255) PATH '$'
)
)
) AS jt;
But the key column is always null.
2
Answers
I don’t think you can extract JSON object keys dynamically into column values in the table.
Use a
UNION
of two queries, one to get thecategory
rows from that property, another to get thespecs
rows.DEMO
Step-by-step fiddle with some remarks.
PS. Adjust the rows ordering with proper ORDER BY clause.