Split JSON "dictonary" into separate rows

Question

colleagues. I have a table like this:

id	param1	param2	config
123	456	789	{"confToUse": "b", "configs": {"a": { "qwe": "rty" }, "b": { "asd", "fgh" } } }

Small explanation: configurations live in a json 'dictionary' property configs, configToUse property points on config with this key to use.

My goal is to get new table where each row is a separate configuration:

id	name	config
x_uniq	a	{ "qwe": "rty" }
y_uniq	b	{ "asd", "fgh" }

Ideally the result looks like this:

id	param1	param2	use	name	config	use_id
x_uniq	456	789	b	a	{ "qwe": "rty" }	y_uniq?
y_uniq	456	789	b	b	{ "asd", "fgh" }	y_uniq?

For mysql version reason I can't use JSON_TABBLE. Only JSON_EXTRACT or so.

At this point could only do this:

SELECT 
    JSON_EXTRACT(tbl.config, "$.configToUse"),
    JSON_EXTRACT(tbl.config, "$.configs") -- I think there has to be some SELECT
FROM 
    configs_table tbl
WHERE
    tbl.id = 123
;

and get:

xxx	yyy
b	{"a": { "qwe": "rty" }, "b": { "asd", "fgh" } }

Please, give me some way to move.

Answer 1

To do this, you need another table (or subquery) that has integers from 0 to N, where N is the greatest number of configs you might have.

Either create a utility table for these integers:

CREATE TABLE N ( N INT PRIMARY KEY );
INSERT INTO N (N) VALUES (0), (1), (2), (3), (4), (5);

Or else generate the table on demand as a subquery:

SELECT ...
FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS N
...

With this, you can do a cross join to get each respective config key in your JSON document.

Here's a demo:

SELECT
  t.param1, t.param2,
  JSON_UNQUOTE(JSON_EXTRACT(t.config, '$.confToUse')) AS `use`,
  t.name,
  JSON_EXTRACT(t.config, CONCAT('$.configs.', t.name)) AS config
FROM (
  SELECT c.param1, c.param2, c.config,
    JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(c.config->'$.configs'), CONCAT('$[',N.N,']'))) AS name
  FROM N CROSS JOIN configs_table c
) AS t
WHERE t.name IS NOT NULL;

Output:

+--------+--------+------+------+----------------+
| param1 | param2 | use  | name | config         |
+--------+--------+------+------+----------------+
|    456 |    789 | b    | a    | {"qwe": "rty"} |
|    456 |    789 | b    | b    | {"asd": "fgh"} |
+--------+--------+------+------+----------------+

I can't tell from your question how you get the values x_uniq and y_uniq, so I'll leave that part of the solution to you.

If you think this solution is complex and difficult, let it serve as an example why using JSON in MySQL leads to a lot of trouble and time-consuming work. It is not convenient or correct to implement a pseudo-database on top of a database. This is sometimes called the Inner-Platform Effect antipattern.