时间戳

首页 > 解决方案 > MySQL 8 Json 文档合并来自多行的数据

mysql - MySQL 8 Json 文档合并来自多行的数据

问题描述

我在多行中有 JSON 文档

第 1 行

{ 
    "data": {
            "level": 1,
            "name": "xyz",
            "property": "value",
            "children": [
                {
                    "level": 2,
                    "name": "xyz-1"
                }
            ]
        }
}

第 2 行

{
    "data": {
            "children": [
                {
                    "level": 2,
                    "name": "xyz-2"
                },
                {
                    "level": 2,
                    "name": "xyz-3"
                }
            ]
        }
}

第 3 行

{
    "data": {
            "children": [
                {
                    "level": 2,
                    "name": "xyz-4"
                }
            ]
        }
}

我想以这种方式使用 MySQL 8 JSON_MERGE_PRESERVE 所以我得到了结果

{ 
    "data": {
            "level": 1,
            "name": "xyz",
            "property": "value",
            "children": [
                {
                    "level": 2,
                    "name": "xyz-1"
                },
                {
                    "level": 2,
                    "name": "xyz-2"
                },
                {
                    "level": 2,
                    "name": "xyz-3"
                },
                {
                    "level": 2,
                    "name": "xyz-4"
                }
            ]
        }
}

我试过了

SELECT JSON_MERGE_PRESERVE(
    '{ "data": { "level": 1, "name": "xyz", "property": "value", "children": [ { "level": 2, "name": "xyz-1" } ] } }',
    '{ "data": { "children": [ { "level": 2, "name": "xyz-2" }, { "level": 2, "name": "xyz-3" } ] } }',
    '{ "data": { "children": [ { "level": 2, "name": "xyz-4" } ] } }'
) as json;

但我想从表中选择 JSON 数据并将其合并，例如

[不工作]

SELECT JSON_MERGE_PRESERVE(a.data_json) from 
    (SELECT data_json FROM data_table
    WHERE name = 'abc') as a

错误消息是错误代码：1582。调用本机函数“JSON_MERGE_PRESERVE”时的参数计数不正确

标签： mysqljsonmysql-8.0

解决方案

您可以评估和使用的众多选项之一是 CTE（公用表表达式） - 13.2.13 WITH（公用表表达式）：

WITH RECURSIVE `cte` AS (
  SELECT
    1 AS `row`,
    `data_json`
  FROM
    `data_table`
  WHERE
    `id` = 1
  UNION ALL
  SELECT
    `cte`.`row` + 1 AS `row`,
    JSON_MERGE_PRESERVE(`cte`.`data_json`, `data_table`.`data_json`) AS `data_json`
  FROM
    `data_table`, `cte`
  WHERE
    `data_table`.`id` = `cte`.`row` + 1
)
SELECT
  JSON_PRETTY(`data_json`)
FROM
  `cte`
ORDER BY
  `row` DESC
LIMIT 1;

请参阅dbfiddle。

推荐阅读