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

How to read JSON in Snowflake?

Im converting a JSON file into columns, but this one column I’m not able to read it, it shows null not the value.

CREATE OR REPLACE TEMPORARY TABLE SAMPLE_J
(
SAMPLE_JSON VARCHAR(100)
);

INSERT INTO SAMPLE_J
(SAMPLE_JSON)
VALUES ('{"ident":["Product:Tires","sender_id:12345","sender_name:John Who","Tire_Condition:New"]}'

--this is my query
SELECT 
   SAMPLE_JSON,
   JSON_EXTRACT_PATH_TEXT(SAMPLE_JSON,'Product') as test1,
   JSON_EXTRACT_PATH_TEXT(SAMPLE_JSON,'Product[0].Product') as test2
FROM SAMPLE_J;

the result is null for test 1 and test 2

This is how JSON file Looks:

{
  "ident": [
    "Product:Tires",
    "sender_id:12345",
    "sender_name:John Who",
    "Tire_Condition:New"
  ]
}
Tags:

2

Answers


  1. 0

    What you have here is an array inside the key ident.

    with data as (
    select $1 as j
    from VALUES ('{"ident":["Product:Tires","sender_id:12345","sender_name:John Who","Tire_Condition:New"]}') 
)

select j
    , json_extract_path_text(j, 'ident') j_ident
    , json_extract_path_text(j, 'ident[0]') j_ident0
from data;

    j_ident0 is the first element of that array, and its value is Product:Tires. That’s not a JSON, just a string.

    Login or Signup to reply.
  2. 0

    And expression of Felipe’s answer, showing accessing the JSON if it has already been converted to JSON/Variant via PARSE_JSON, and then accessing via the inline methods is:

    with data as (
    select
        parse_json($1) as json
    from VALUES 
        ('{
  "ident": [
    "Product:Tires",
    "sender_id:12345",
    "sender_name:John Who",
    "Tire_Condition:New"
  ]
}') 
)
select json
    ,json:"ident" as j_ident
    ,json:"ident"[0] as j_ident0
from data;

    but looking at that data, it’s not in of itself, an JSON object, but an array of strings, and that implies there is a chance the order of the data might be variable.

    So that array itself might be safer to process via a FLATTEN on the array, and taking just the part with the desired prefix:

    with data as (
    select
        parse_json($1) as json
    from VALUES 
        ('{"ident": ["Product:Tires", "sender_id:12345", "sender_name:John Who", "Tire_Condition:New" ]}'),
        ('{"ident": ["sender_id:12346", "sender_name:Tom Who", "Tire_Condition:Old", "Product:Tires" ]}')
)
select 
    json:"ident"[0] as j_ident0
from data;

    enter image description here

    thus using a FLATTEN and a WHERE:

    with data as (
    select
        $1 as str
    from VALUES 
        ('{"ident": ["Product:Tires", "sender_id:12345", "sender_name:John Who", "Tire_Condition:New" ]}'),
        ('{"ident": ["sender_id:12346", "sender_name:Tom Who", "Tire_Condition:Old", "Product:Tires" ]}')
)
select 
    d.str
    ,json_extract_path_text(d.str, 'ident[0]') as now_wrong
    ,f.value as but_correct_here
    --,f.*
from data as d,
lateral flatten(input=>parse_json(json_extract_path_text(d.str, 'ident'))) as f
where f.value like 'Product:%'
;

    we get:

    enter image description here

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

Back To Top
Search