Expand JSON with unknown keys to rows with MySQL JSON_TABLE

Question

I have a MySQL 8.0.22 JSON column containing objects with keys that aren't known in advance:

'{"x": 1, "y": 2, "z": 3}'
'{"e": 4, "k": 5}'

I want to use JSON_TABLE to expand these values into multiple rows containing key value pairs:

key	value
x	1
y	2
z	3
e	4
k	5

The difficulty of course is that the keys aren't known a priori. The best thing I've come up with is...


SET @json_doc = '{"x": 1, "y": 2, "z": 3}';

SELECT a.seq, b.k, a.v

    FROM

    JSON_TABLE(
        @json_doc,
        "$.*"
        COLUMNS(
            seq FOR ordinality,
            v INT PATH "$"
        )
    ) AS a,

    JSON_TABLE(
        JSON_KEYS(@json_doc),
        "$[*]"
        COLUMNS(
            seq FOR ordinality,
            k CHAR(1) PATH "$"
        )
    ) AS b

    WHERE a.seq = b.seq;

This feels strange because it uses two JSON_TABLE calls, does a cross join on the values and keys, then keeps the ones that align. I'd like to find a simpler query like this...

SELECT a.seq, b.k, a.v

    FROM
    
    JSON_TABLE(
        @json_doc,
        "$.*"
        COLUMNS(
            seq FOR ordinality,
            k CHAR(1) PATH "?"  -- <-- what do I put here to find each key?
            v INT PATH "$"
        )
    ) AS a,

I know this problem can probably be solved with CTEs or a numbers table and JSON_EXTRACT. But, I'd like to find something performant and readable if possible.

I needed only sequence , its worked for me

Harikrushna Patel
– Harikrushna Patel

2023-09-02 08:36:41 +00:00
Commented Sep 2, 2023 at 8:36 — Harikrushna Patel
– Harikrushna Patel, Commented Sep 2, 2023 at 8:36

Barbaros Özhan · Accepted Answer · 2021-01-02 13:52:00Z

3

You can use enumarete by using ROW_NUMBER() window function while determining the key values through use of JSON_KEYS(), and then extract the respective keys by using JSON_EXTRACT() from the arrays we got such as

WITH k AS
(
SELECT *, 
       ROW_NUMBER() OVER(PARTITION BY `jsdata` ORDER BY value DESC) AS rn,
       JSON_KEYS(`jsdata`) AS jk
  FROM `tab` AS t
  JOIN JSON_TABLE(`jsdata`,'$.*' COLUMNS (value INT PATH '$')) j
)
SELECT JSON_UNQUOTE(JSON_EXTRACT(jk, CONCAT('$[',rn-1,']'))) AS "key", 
       value
  FROM k

or use the following query as being more straightforward

SELECT JSON_UNQUOTE(
       JSON_EXTRACT(JSON_KEYS(`jsdata`), 
                    CONCAT('$[',
                    ROW_NUMBER() OVER(PARTITION BY `jsdata` ORDER BY value DESC)-1,
                    ']'))
                   ) AS "key", value
  FROM `tab` AS t
  JOIN JSON_TABLE(`jsdata`,'$.*' COLUMNS (value INT PATH '$')) j

Demo

edited Jan 2, 2021 at 13:52

answered Jan 2, 2021 at 12:21

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Comments

Evan Muller · Accepted Answer · 2023-03-18 01:50:34Z

2

In the same vein as EricW's answer but without the CTE and with an explicit cross join...

SELECT k, JSON_EXTRACT(json_data, concat('$.', k)) as v
FROM the_table t
CROSS JOIN JSON_TABLE(
    json_keys(json_data),
    '$[*]' COLUMNS(k VARCHAR(10) path '$')
) t2;

answered Mar 18, 2023 at 1:50

Evan Muller

213 bronze badges

Comments

EricW · Accepted Answer · 2021-11-10 02:33:26Z

1

Try to do JSON_EXTRACT directly after you got the JSON_KEYS as rows:

  WITH j AS (
      SELECT CAST('{"a": 1, "b": "-1", "c": null}' AS JSON) o UNION ALL
      SELECT CAST('{"x": 2, "y": "-2", "z": null}' AS JSON)
  )
SELECT k, JSON_EXTRACT(j.o, CONCAT('$."', jt.k, '"')) v
  FROM j
     , JSON_TABLE(JSON_KEYS(o), '$[*]' COLUMNS (k VARCHAR(200) PATH '$')) jt;

The answer by Barbaros can solve your problem with the demo data you provided, but it may not get what you want if your json objects have same value under different keys.

answered Nov 10, 2021 at 2:33

EricW

5214 silver badges7 bronze badges

Collectives™ on Stack Overflow

Expand JSON with unknown keys to rows with MySQL JSON_TABLE

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related