Question 289523 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

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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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?

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

Back To Top
Search