sql - 有没有办法用雪花展平 json 列表？

Question

我有一个列是我的表中的一个列表我想展平/展开到列，所以我可以从这个转换它

{"name a":1,"name b":1,"name c":1,"name d":1,"name e":1}

对此

|name a |name b|name c|name d |name e|

| 1 |1 |1 |1 |1 |

每个都有自己的专栏

标签： sqljsonsnowflake-cloud-data-platform

Answer 1

我找不到这样做的动态方式。无论您是先展平列然后将其重新旋转，还是以下面的这种方式进行，您都需要引用列名。可能有一种方法可以在存储过程中动态执行此操作，或者在 Python 或其他语言中动态创建 SQL：

-- Creating a Mock table to query
WITH json_table AS (
  SELECT TO_VARIANT(PARSE_JSON('{"name a":1,"name b":1,"name c":1,"name d":1,"name e":1}')) AS json_field
  UNION ALL 
  SELECT TO_VARIANT(PARSE_JSON('{"name a":2,"name b":2,"name c":2,"name d":2,"name e":2}')) AS json_field

)

SELECT json_field:"name a" AS "name a"
      ,json_field:"name b" AS "name b"
      ,json_field:"name c" AS "name c"
      ,json_field:"name d" AS "name d"
      ,json_field:"name e" AS "name e"
FROM json_table j
;