Search code examples
splitmariadbmysql-jsonmariadb-10.4

Split JSON "dictonary" into separate rows

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.

Solution

  • 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.