0

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

Arun Mandalapu
  • 1
  • 2

1 Answers1

0

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}', ' '));
Greg Pavlik
  • 10,089
  • 2
  • 12
  • 29
  • Sorry my bad, I haven't explained properly what I need to do. I am trying to load a `.jsonl` file in to the snowflake table of column type variant.I need to load, each json line into a new row. (it may be using new line delimiter). But I not able find a right solution. Any help on this much appreciated. Thanks a heaps. – Arun Mandalapu Mar 16 '20 at 04:28
  • I took away the second option for clarity. The code section does what you're describing. It splits the JSONL by the space between them and turns them into a table of JSON variants. – Greg Pavlik Mar 16 '20 at 04:44