MySQL 8 Json文档合并多行数据

Question

我将多行中的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‘的调用中的参数计数不正确

Answer 1

可以评估和使用的众多选项之一是CTE (常用表表达式)- 13.2.13 WITH (Common Table Expressions)

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。