sql - 在 SQL 中将 SQL 表转换为嵌套的 JSON

Question

我的雪花数据库中有这样的行：

+-----+-----+-----+
| Foo | Bar | Baz |
+-----+-----+-----+
| A   | a   | []  |
| A   | b   | []  |
| B   | a   | []  |
| B   | b   | []  |
+-----+-----+-----+

我想将其转换为：

  "A": {
    "a": [],
    "b": []
  },
  "B": {
    "a": [],
    "b": []
  }

标签： sqlsnowflake-cloud-data-platform

Answer 1

Snowflake 允许使用 SQL 达到预期的效果：

CREATE OR REPLACE TABLE t
AS
SELECT 'A' AS foo, 'a' AS bar, PARSE_JSON('[]') AS Baz
UNION ALL SELECT 'A' AS foo, 'b' AS bar, PARSE_JSON('[]') AS Baz
UNION ALL SELECT 'B' AS foo, 'a' AS bar, PARSE_JSON('[]') AS Baz
UNION ALL SELECT 'B' AS foo, 'b' AS bar, PARSE_JSON('[]') AS Baz;


SELECT OBJECT_AGG(foo, s) AS result
FROM (SELECT foo, OBJECT_AGG(bar, baz) AS s
      FROM t
      GROUP BY foo) sub;

输出：

{
  "A": {
    "a": [],
    "b": []
  },
  "B": {
    "a": [],
    "b": []
  }
}