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

Question

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.

There is actually a task for this at MariaDB development: jira.mariadb.org/browse/MDEV-30145 — Thomas Jensen
– Thomas Jensen, Commented Feb 6, 2023 at 17:30

lemon · Accepted Answer · 2023-01-28 20:12:10Z

2

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.

edited Jan 28, 2023 at 20:12

answered Jan 28, 2023 at 19:58

lemon

15.6k6 gold badges23 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Thomas Jensen Over a year ago

This is a good workaround, but I find it hard to believe that there is'nt any build-in functionality in JSON_TABLE to solve this task.

lemon Over a year ago

If someone will post a query while using JSON_TABLE only, I will be learning with you. As far as I've tried, it became messier due to subqueries.

Barbaros Özhan · Accepted Answer · 2023-01-29 06:45:59Z

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

Thomas Jensen · Accepted Answer · 2023-01-29 14:36:32Z

1

I answer to my own question: I'm sorry that there apparently is'nt any native option to reference the key names in a json object with JSON_TABLE (yet), and the two workarounds currently posted are great. I ended up using a mixture from both:

SET @json = '{ "1": [1, 123.25], "10": [2, 110.5], "100": [3, 105.75] }';

SELECT
    col1,
    JSON_EXTRACT(@json, CONCAT('$."', col1, '"[0]')) col2,
    JSON_EXTRACT(@json, CONCAT('$."', col1, '"[1]')) col3
FROM JSON_TABLE(JSON_KEYS(@json), '$[*]' COLUMNS (col1 varchar(20) PATH '$')) t1;

answered Jan 29, 2023 at 14:36

Thomas Jensen

411 silver badge5 bronze badges

2 Comments

lemon Over a year ago

Although this doesn't look like a scalable solution... If tomorrow you happen to have 20 columns in your json, would you use 20 JSON_EXTRACT?

Thomas Jensen Over a year ago

@lemon You're right, in that case I would probably use your solution. Also, if there was many rows, it would be a better solution. But for my use case, the datasets are like in my example, and therefore my answer seems to be the least complex/most efficient.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related