How to load .jsonl into a snowflake table variant?

0
votes

How to load .jsonl into a table variant as json of snowflake 

create or replace table sampleColors (v variant);

insert into
   sampleColors
   select
      parse_json(column1) as v
   from
   values
     ( '{r:255,g:12,b:0} {r:0,g:255,b:0} {r:0,g:0,b:255}')
    v;

select * from sampleColors;

Error parsing JSON: more than one document in the input

1
snowflake-cloud-data-platformsnowflake-schemasnowflake-task

1 Answers

2
votes

If you want each RGB value in its own row, you need to split the JSONL to a table with one row per JSON using a table function like this:

insert into
    sampleColors
select parse_json(VALUE) 
    from table(split_to_table( '{r:255,g:12,b:0} {r:0,g:255,b:0} {r:0,g:0,b:255} {c:0,m:1,y:1,k:0} {c:1,m:0,y:1,k:0} {c:1,m:1,y:0,k:0}', ' '));