MariaDB/MySQL - Convert keys and values from json object into rows, using JSON

Using Mariadb 10.6 - In the following example, I try to convert the entries of the json object into table rows:

SELECT *
FROM JSON_TABLE('{ 
   "1": [1, 123.25], 
   "10": [2, 110.5], 
   "100": [3, 105.75] 
}', '$.*' COLUMNS (
    col1 decimal(13,2) PATH '$',
    col2 int PATH '$[0]',
    col3 decimal(17,2) PATH '$[1]'
)) table1

The result is:

col1	col2	col3
NULL	1	123.25
NULL	2	110.50
NULL	3	105.75

Is there any way to fill "col1" with the property keys ("1", "10", "100")?

I guess there is some "key word" to reference the key, but I can't find any information on this in the docs from MariaDB or MySQL.

I already made a routine that creates a temporary table by looping output from JSON_KEYS, but it would be more elegant if I can use JSON_TABLE for this job.

CodePudding user response：

Here's one way to do it without routines:

extract your json values using JSON_TABLE, alongside a row number using FOR ORDINALITY
extract your keys using JSON_KEYS
for each record, extract the i-th key corresponding to i-th ranking value, given by row number, using JSON_EXTRACT

SELECT JSON_EXTRACT(JSON_KEYS(@json), 
                    CONCAT('$[', table1.rowid-1, ']')) AS col1, 
       table1.col2,
       table1.col3
FROM JSON_TABLE(@json, '$.*' COLUMNS (
    rowid FOR ORDINALITY,
    col2 int PATH '$[0]',
    col3 decimal(17,2) PATH '$[1]'
)) table1

Output:

col1	col2	col3
"1"	1	123.25
"10"	2	110.50
"100"	3	105.75

Check the demo here.

CodePudding user response：

This is an other way to do it using CROSS JOIN, JSON_TABLE & JSON_KEYS:

JSON_KEYS(json) will give us ["1", "10", "100"]

CROSS JOIN is used to generate multi rows from ["1", "10", "100"]

WITH data AS
( SELECT '{
        "1": [1, 123.25], 
        "10": [2, 110.5], 
        "100": [3, 105.75] 
       }' as json
)
SELECT k.`key`, c.col2, c.col3
FROM data
CROSS JOIN JSON_TABLE( JSON_KEYS(json), '$[*]' COLUMNS (
               rowid FOR ORDINALITY,
               `key` TEXT PATH '$'
             )
           ) k
INNER JOIN (
  SELECT cols.*
  FROM data, JSON_TABLE(json, '$.*' COLUMNS (
    rowid FOR ORDINALITY,
    col2 int PATH '$[0]',
    col3 decimal(17,2) PATH '$[1]'
  )) as cols
) AS c on c.rowid = k.rowid;

demo here