前端之家

  1. 首页
  2. 问答

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-1"
                },"name": "xyz-3"
                },"name": "xyz-4"
                }
            ]
        }
}

我尝试过

SELECT JSON_MERGE_PRESERVE(
    '{ "data": { "level": 1,"children": [ { "level": 2,"name": "xyz-1" } ] } }','{ "data": { "children": [ { "level": 2,"name": "xyz-2" },{ "level": 2,"name": "xyz-3" } ] } }',"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”的调用中的参数计数不正确

zhu_yun_yun 回答：MySQL 8 Json文档合并多行数据

您可以评估和使用的众多选项之一是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。

jsonmysqlmysql-8.0
本文链接：https://www.f2er.com/3029176.html

大家都在问