CODEWITHSUNDEEP
mysqlmysql-json
ganLover
ganLover

Reputation: 53

Flatten JSON object in MYSQL

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.

Upvotes: 0

Views: 102

Answers (2)

Akina
Akina

Reputation: 42764

SELECT jsonkeystable.keyname,
       jsonvaluestable.value
FROM test
CROSS JOIN JSON_TABLE(
  JSON_KEYS(test.jsondata),
  '$[*]' COLUMNS (
    keyid FOR ORDINALITY,
    keyname VARCHAR(64) PATH '$'
    )
  ) jsonkeystable
CROSS JOIN JSON_TABLE(
  JSON_EXTRACT(test.jsondata, CONCAT('$.', jsonkeystable.keyname)),
  '$[*]' COLUMNS (
    valueid FOR ORDINALITY,
    value VARCHAR(64) PATH '$'
    )
  ) jsonvaluestable
ORDER BY test.id, jsonkeystable.keyid, jsonvaluestable.valueid
keyname value
specs 4g
specs 2gb ram
category mobiles
specs 2gb ram
category computer

Step-by-step fiddle with some remarks.

PS. Adjust the rows ordering with proper ORDER BY clause.

Upvotes: 1

Barmar
Barmar

Reputation: 782344

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 the category rows from that property, another to get the specs rows.

SELECT 'category' AS `key`, jt.value
FROM table_name
CROSS JOIN JSON_TABLE( 
    column_name,
    "$.category[*]" COLUMNS (
        value TEXT PATH '$'
    )
  ) AS jt

UNION ALL

SELECT 'specs' AS `key`, jt.value
FROM table_name
CROSS JOIN JSON_TABLE( 
    column_name,
    "$.specs[*]" COLUMNS (
        value TEXT PATH '$'
    )
  ) AS jt

DEMO

Upvotes: 0

Related Questions