Question 109300 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – Conditional lateral flatten in Snowflake

I have a table with json variants that contain keys which may or may not be there depending on the API response.

example table:

create or replace temporary table test (variant_col variant);

insert into test(variant_col)
select parse_json('{"response": [{"key1":[1,2,3]}, {"key2":[7,8,9]}]}');
insert into test(variant_col)
select parse_json('{"response": [{"key2":[7,8,9]}]}');

I’d like to lateral flatten them based on their keys, and use key1 if it exists, else use key2 (which will always exist)

SELECT
    iff(f2.value::int is null, 'key2', 'key1') as value_type,
    ifnull(f2.value::int, f3.value::int) as desired_value
FROM
    test,
    LATERAL FLATTEN(input => response) as f1,
    LATERAL FLATTEN(input => f1.value:key1) as f2,
    LATERAL FLATTEN(input => f1.value:key2) as f3
;
Tags:

2

Answers


  1. 0

    We can solve this by giving an id to each row, and then we can union all the results of 2 queries – depending on the existence of key1:

    with data as (
    select row_number() over(order by 0) id, * 
    from test
), rows_with_key1 as (
    select data.*, value v
    from data
        , lateral flatten(input => variant_col:response) as f1
    where f1.value:key1 is not null
), rows_without_key1 as (
    select data.*, value v
    from data
        , lateral flatten(input => variant_col:response) as f1
    where id not in (select id from rows_with_key1)
)


select id, value, variant_col
from rows_with_key1
    , lateral flatten(input => v:key1) ff
union all
select id, value, variant_col
from rows_without_key1
    , lateral flatten(input => v:key2)
;
    Login or Signup to reply.
  2. 0

    If flatten is excluding the row from the result set, you can use OUTER => true, which will return a null row for zero-row expansions

    SELECT
    iff(f2.value::int is null, 'key2', 'key1') as value_type,
    ifnull(f2.value::int, f3.value::int) as desired_value
FROM
    test,
    LATERAL FLATTEN(input => variant_col:response) as f1,
    LATERAL FLATTEN(input => f1.value:key1) as f2,
    LATERAL FLATTEN(input => f1.value:key2, OUTER => true) as f3
;

    If TRUE, exactly one row is generated for zero-row expansions (with
    NULL in the KEY, INDEX, and VALUE columns).

    https://docs.snowflake.com/en/sql-reference/functions/flatten

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search