How to create a subset from a variant column in snowflake?

Question

For example, suppose my variant column "xyz" consists of data like this:

{
  "post_new_visits": "Repeat",
  "post_new1_week": "Thursday",
  "post_new2_appt": "Weekday",
  "post_new3_site": "12:50AM",
  "post_new4_channel": "5.0",
  "pre_new2_appt": "Weekday",
  "pre_new3_site": "12:50AM",
  "pre_new4_channel": "5.0"
}

I want a new variant column from above variant column, which should only have "post*" key values, something like below,output should be like this.

{
  "post_new_visits": "Repeat",
  "post_new1_week": "Thursday",
  "post_new2_appt": "Weekday",
  "post_new3_site": "12:50AM",
  "post_new4_channel": "5.0",
}

Is there any way i can acheive this?

There is a code format option in the question editor - I recommend you use it. — Dale K, May 20 '20 at 09:55

Gokhan Atil · Accepted Answer · 2020-05-20T11:41:31.237

0

Maybe you can flatten and re-construct the JSON. For example:

create table tmp ( v variant )
as 
select
    parse_json(
        '{  "post_new_visits": "Repeat",
          "post_new1_week": "Thursday",
          "post_new2_appt": "Weekday",
          "post_new3_site": "12:50AM",
          "post_new4_channel": "5.0",
          "pre_new2_appt": "Weekday",
          "pre_new3_site": "12:50AM",
          "pre_new4_channel": "5.0"}'
    )
union all                              
select
    parse_json(
        '{  "post_new_visits": "New",
          "post_new1_week": "Friday",
          "post_new2_appt": "Weekday",
          "post_new3_site": "13:50AM",
          "post_new4_channel": "4.0",
          "pre_new2_appt": "Weekday",
          "pre_new3_site": "14:50AM",
          "pre_new4_channel": "2.0"}'
    );                                      




select
    OBJECT_AGG(v2.key, v2.value)
from
    tmp,
    lateral flatten(input => v) v2
where
    v2.key like 'post%'
group by
    v2.seq;

edited May 20 '20 at 11:41

answered May 20 '20 at 10:38

Gokhan Atil

9,278
2
11
24

The solution is working for one value(given as a parameter in parse_json) but its not working for all the column values, i meant cant apply for a table – Manu May 20 '20 at 11:19
Do you have multiple variant columns or multiple rows? It should work for multiple rows (all table). – Gokhan Atil May 20 '20 at 11:38
with tmp as ( select parse_json(variant_column) v from table_name ) select OBJECT_AGG(v2.key, v2.value) as persisted_vars from tmp, lateral flatten(input => v) v2 where v2.key like 'post_evar%' group by v2.seq; – Manu May 20 '20 at 11:49
If we use flatten, it's giving a huge performance impact for example, in my case, I have around 500 key-value pairs and a million records also, so performance is too low, is there any other way instead of flattening, I mean UDF's, SP's. – Manu May 21 '20 at 05:26
FLATTEN/OBJECT_AGG is indeed quite slow. But you can always write a JavaScript UDF doing exactly what you want. – Marcin Zukowski May 22 '20 at 18:26

How to create a subset from a variant column in snowflake?

1 Answers1