如何在雪花中解析这个json

Question

{
  "segmentId": "b204c220-ea8d-4cf4-b579-30eb59a1a2a4",
  "diffFields": [
    {
      "fieldName": "name",
      "valueBefore": null,
      "valueAfter": "new-segment-name"
    },
    {
      "fieldName": "active",
      "valueBefore": null,
      "valueAfter": true
    }
  ]
}

在上面的JSON中，我有一个difffields的数组。我试图在雪花上解析此列，而不是行阵列而不是行。 我尝试了变平，但这将其弄平为行。

我试图在DBT中解析此此表，以创建上面的JSON，其表结构为

create table some_table (
field_one,
--if `name` is present in the above json I want that to be 2nd column
-- if `active` is present in the above json i want that to be 3nd column 
)
)

Answer 1

我会像以下情况下给它那样的弄平

WITH data as (
    select parse_json('
{
  "segmentId": "b204c220-ea8d-4cf4-b579-30eb59a1a2a4",
  "diffFields": [
    {
      "fieldName": "name",
      "valueBefore": null,
      "valueAfter": "new-segment-name"
    },
    {
      "fieldName": "active",
      "valueBefore": null,
      "valueAfter": true
    }
  ]
}') as json
)
select 
    json:segmentId::text as seg_id,
    f.value:fieldName::text as fieldName,
    f.value:valueBefore as valueBefore,
    f.value:valueAfter as valueAfter 
from data, table(flatten(input=>json:diffFields)) f

seg_id

fieldname	value	的	之前
b204c220-e8d-4cf4-b579-30eb59a1a2a4	名称num num	null	new-segment-name“
b204c220-ea8d-ea8d-ea8d-ea8d-4cf4-b579-309-30eb59a1a2a	nul	“	：

）空。因此，您想使用 is_null_value 测试和covert 要

选择数组零件：

select json:segmentId::text
    ,max(iff(f.value:fieldName::text = 'name', f.value, null)) as name_object
    ,max(iff(f.value:fieldName::text = 'active', f.value, null)) as active_object
from data, table(flatten(input=>json:diffFields)) f
group by 1;

json

：segmentId :: text	name_object	active_object
b204c220-ea8d-4cf4-b579-30eb59a1a2a4	{“ fieldname”：“ name”，“ name”，“ new-segnmegname”，“ new-seak-name”，“ new-name” name“”， “：null}	{“ fieldName”：“ active”，“ value ted”：true，dureateborfore'：null}

Answer 2

您可以使用QuickTable连接雪花并进行JSON PARSE使用QuickTable。 QuickTable可以生成与雪花兼容的相关SQL。