Question 110008 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

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

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.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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;

  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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

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

Back To Top
Search