How to parse this json in snowflake

Question

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

In the above json I have an array of diffFields . I am trying to parse this in snowflake get the array of columns instead of rows. I tried flatten, but this flatten it as rows.

I am trying to parse this in dbt to create another table from the above json with table structure as

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

I would flatten it like

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

which gives:

SEG_ID	FIELDNAME	VALUEBEFORE	VALUEAFTER
b204c220-ea8d-4cf4-b579-30eb59a1a2a4	name	null	"new-segment-name"
b204c220-ea8d-4cf4-b579-30eb59a1a2a4	active	null	true

but those variant data nulls are not real nulls. so you want to use something like is_null_value to test and covert to real nulls

To select array parts:

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;

gives:

JSON:SEGMENTID::TEXT	NAME_OBJECT	ACTIVE_OBJECT
b204c220-ea8d-4cf4-b579-30eb59a1a2a4	{ "fieldName": "name", "valueAfter": "new-segment-name", "valueBefore": null }	{ "fieldName": "active", "valueAfter": true, "valueBefore": null }