主页 / dba / 问题 / 329988
Accepted
Ian Newson
Asked: 2023-08-05 03:32:07 +0800 CST

json_merge_preserve 对 JSON 数据列

  • 772

json_merge_preserve接受两个或多个 JSON 文档并将它们合并为一个。然而，函数定义似乎只接受固定数量的参数。

所以如果我有一个像这样的表：

create table jsons (data longtext);

其中有任意行数，我该如何使用json_merge_preserve？

mysql

1 个回答

  1. Best Answer

    可能的解决方案：

    CREATE TABLE test (json_val JSON)
SELECT '{ "a": 1, "b": 2, "c": 3}' json_val UNION ALL
SELECT '{ "a": 4, "c": 5, "d": 6}'          UNION ALL
SELECT '{ "a": 7, "b": 8, "e": 9}'          ;
SELECT CAST(json_val AS CHAR) FROM test;
    CAST（json_val AS CHAR）
    {“a”：1，“b”：2，“c”：3}
    {“a”：4，“c”：5，“d”：6}
    {“a”：7，“b”：8，“e”：9} 
    WITH RECURSIVE
cte1 AS (
  SELECT json_val, ROW_NUMBER() OVER () rn
  FROM test
),
cte2 AS (
  SELECT json_val, rn
  FROM cte1
  WHERE rn = 1
UNION ALL
  SELECT JSON_MERGE_PRESERVE(cte2.json_val, cte1.json_val), cte1.rn
  FROM cte1
  JOIN cte2 ON cte1.rn = cte2.rn + 1
)
SELECT CAST(json_val AS CHAR)
FROM cte2
ORDER BY rn DESC LIMIT 1;
    CAST（json_val AS CHAR）
    {“a”: [1, 4, 7], “b”: [2, 8], “c”: [3, 5], “d”: 6, “e”: 9}

    小提琴

    • 1

相关问题