split - 将 JSON“字典”拆分为单独的行

Question

同事。我有一张这样的桌子：

ID	参数1	参数2	配置
123	456	789	{"confToUse": "b", "configs": {"a": { "qwe": "rty" }, "b": { "asd", "fgh" } } }

小解释：配置存在于 json 'dictionary' 属性configs中，configToUse配置上的属性点与此键一起使用。

我的目标是获得新表，其中每一行都是单独的配置：

ID	姓名	配置
x_uniq	一个	{ "qwe": "rty" }
y_uniq	b	{ "asd", "fgh" }

理想情况下，结果如下所示：

ID	参数1	参数2	利用	姓名	配置	使用_id
x_uniq	456	789	b	一个	{ "qwe": "rty" }	y_uniq？
y_uniq	456	789	b	b	{ "asd", "fgh" }	y_uniq？

由于 mysql 版本原因，我不能使用 JSON_TABBLE。只有 JSON_EXTRACT 左右。

此时只能这样做：

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
;

并得到：

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

请给我一些移动的方法。

标签： splitmariadbmysql-jsonmariadb-10.4

Answer 1

为此，您需要另一个具有从 0 到N的整数的表（或子查询） ，其中N是您可能拥有的最大配置数。

为这些整数创建一个实用程序表：

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

或者根据需要生成表作为子查询：

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

有了这个，您可以进行交叉连接以获取 JSON 文档中的每个相应配置键。

这是一个演示：

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;

输出：

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

我无法从您的问题中看出您如何获得值x_uniq和y_uniq，所以我将把解决方案的那部分留给您。

如果您认为这个解决方案复杂且困难，不妨举例说明为什么在 MySQL 中使用 JSON 会导致很多麻烦和耗时的工作。在数据库之上实现伪数据库既不方便也不正确。这有时被称为内部平台效应反模式。