CODEWITHSUNDEEP
sqljsonsnowflake-cloud-data-platform
Биляна Митова
Биляна Митова

Reputation: 11

Extract json data

I want to be able to extract all values from json in snowflake contains "_sum".

I have this format for example:

{"260196":7877,"260206":2642,"260216":7620,"260226":7560,"_sum":25699}
{"260196":9804,"260206":9804,"260216":9487,"260226":9411,"_sum":38506}

I want this results:

Total Value
_sum 25699
_sum 38506

I try to used this:

, TO_VARCHAR(GET_PATH(PARSE_JSON(x), '_sum'))

but I received error

100069 (22P02): Error parsing JSON: unknown keyword "N", pos 2

Upvotes: 0

Views: 70

Answers (2)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26043

Much like's Sergu's answer:

using the VALUES to have two strings "that are JSON" we can PARSE_JSON like you have then, access it via the : accessor, or via get_path form. Your code works just fine on this example data, and then we can change ether access method (s1, s2) to TEXT (the same thing as VARCHAR) via inline case ::

select
    parse_json($1) as j
    ,j:_sum as s1
    ,get_path(j, '_sum') as s2
    ,TO_VARCHAR(GET_PATH(PARSE_JSON($1), '_sum')) as works_here
    ,s1::text as t1
    ,s2::text as t2
from values 
    ('{"260196":7877,"260206":2642,"260216":7620,"260226":7560,"_sum":25699}'),
    ('{"260196":9804,"260206":9804,"260216":9487,"260226":9411,"_sum":38506}')

gives all working stuff as expected:

enter image description here

So the error message you post means, "the text you have is not JSON" in simplest form:

from values 
    ('N{"260196":7877,"260206":2642,"260216":7620,"260226":7560,"_sum":25699}')

gives:

100069 (22P02): Error parsing JSON: unknown keyword "N", pos 2

so your "JSON" is not valid JSON, if you are expect some bad input you can use the TRY_PARSE_JSON function, which returns NULL on failure to parse. But if you are not expected bad data, it sounds like you need to find what JSON strings you have the start with a N.

It makes me think of JOSNL that has been split perhaps wrongly, or something like that? perhaps a newline in the JSON text, that then got stringfied, and thus became a \\N type thing?

Upvotes: 1

Sergiu
Sergiu

Reputation: 4598

What about something like this:

create or replace table test_variant(v variant);
insert into test_variant select parse_json('{"260196":7877,"260206":2642,"260216":7620,"260226":7560,"_sum":25699}');
insert into test_variant select parse_json('{"260196":9804,"260206":9804,"260216":9487,"260226":9411,"_sum":38506}');
select * from test_variant;

I see:

V
{   "260196": 7877,   "260206": 2642,   "260216": 7620,   "260226": 7560,   "_sum": 25699 }
{   "260196": 9804,   "260206": 9804,   "260216": 9487,   "260226": 9411,   "_sum": 38506 }

Then to get your desired output:

select '_sum' as Total, v:"_sum" as Value from test_variant;

I get:

TOTAL   VALUE
_sum    25699
_sum    38506

Upvotes: 0

Related Questions