如何在雪花中弄平json？ SQL

发布于 2025-02-09 22:58:05 字数 286 浏览 1 评论 0原文

我有一个表“ table_1”，带有一个名为“值”的列，它只有一个条目。列中的条目是一个JSON，看起来

{
  "c1": "A",
  "c10": "B",
  "c100": "C",
  "c101": "D",
  "c102": "E",
  "c103": "F",
  "c104": "G",
.......
}

我想将此JSON分为两个列，其中一个列包含键（C1，C10等），第二列包含该键的关联值（a，a， B等）。有什么办法可以做到吗？我的json中大约有125个钥匙

原文

I have a table "table_1" with one column called "Value" and it only has one entry. The entry in the column is a json that looks like

{
  "c1": "A",
  "c10": "B",
  "c100": "C",
  "c101": "D",
  "c102": "E",
  "c103": "F",
  "c104": "G",
.......
}

I would like to just separate this json into two columns, where one column contains the keys (c1, c10 etc), and the second columns contains the associated values for that key (A, B etc). Is there a way I can do this? There are about 125 keys in my json

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

折戟 2025-02-16 22:58:05

可以使用扁平函数来实现它：

CREATE OR REPLACE TABLE tab
AS
SELECT PARSE_JSON('{
  "c1": "A",
  "c10": "B",
  "c100": "C",
  "c101": "D",
  "c102": "E",
  "c103": "F",
  "c104": "G",
}') AS col;

SELECT KEY, VALUE::TEXT AS value
FROM tab
,TABLE(FLATTEN (INPUT => tab.COL));

输出：

It is possible to achieve it using FLATTEN function:

CREATE OR REPLACE TABLE tab
AS
SELECT PARSE_JSON('{
  "c1": "A",
  "c10": "B",
  "c100": "C",
  "c101": "D",
  "c102": "E",
  "c103": "F",
  "c104": "G",
}') AS col;

SELECT KEY, VALUE::TEXT AS value
FROM tab
,TABLE(FLATTEN (INPUT => tab.COL));

Output: