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

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0 
    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search