Extracting variant/json data in Snowflake

Question

I have a column, let's say variant_column, which has data that looks exactly like this (this would be one row of data):

[
  {
    "key": "column_name_1",
    "value": "value_goes_here"
  },
  {
    "key": "metadata",
    "value": "{this_could_be_another_huge_json_here}"
  },
  {
    "key": "column_name_2",
    "value": "value_goes_here_again"
  },
  {
    "key": "column_name_3",
    "value": "value_goes_here_yet_again"
  }
]

How can I query for a specific key value? I.e., I want the results of my query to look like:

column_name_1
value_goes_here
more values...for each row of data

Each row will always have "key": "column_name_1" and an associated value which can change. I've tried:

get_path(variant_column, '"key": "column_name_1"')
get_path(variant_column, 'column_name_1')

and a few others, but the fact that every entry in the variant will have "key" and "value" is messing me up. How can I create a single column from "column_name_1" (they key will always be "column_name_1") and the associated "value" (it will always be called "value", but the actual data for "value" will be different).

Mike Walton Mike Walton · Accepted Answer · 2021-02-02T22:44:46

Not the ideal JSON structure, but you can still flatten it out, use some case statements, and aggregate things back together again. Try something like this:

WITH x AS (
SELECT parse_json('[
  {
    "key": "column_name_1",
    "value": "value_goes_here"
  },
  {
    "key": "metadata",
    "value": "{this_could_be_another_huge_json_here}"
  },
  {
    "key": "column_name_2",
    "value": "value_goes_here_again"
  },
  {
    "key": "column_name_3",
    "value": "value_goes_here_yet_again"
  }
]') as var
)
SELECT f.seq,
       MAX(CASE WHEN f.value:key::varchar = 'column_name_1' THEN f.value:value::varchar END) as column_name_1,
       MAX(CASE WHEN f.value:key::varchar = 'column_name_2' THEN f.value:value::varchar END) as column_name_2,
       MAX(CASE WHEN f.value:key::varchar = 'column_name_3' THEN f.value:value::varchar END) as column_name_3,
       MAX(CASE WHEN f.value:key::varchar = 'metadata' THEN f.value:value::variant END) as metadata
FROM x,
LATERAL FLATTEN(input=>var) f
GROUP BY f.seq
;

Extracting variant/json data in Snowflake

1 Answers