Parse JSON using Snowflake SQL

Question

I have a JSON object that's written in a weird way.

> {"custom": [ { "name": "addressIdNum", "valueNum": 12345678},  {
> "name": "cancelledDateAt", "valueAt": "2017-02-30T01:43:04.000Z" }] }

Not sure how to parse something like this. The keys are addressIdNum and cancelledDateAt and the values are 12345678 and 2017-02-30T01:43:04.000Z respectively.

How do I parse this using Snowflake SQL?

Thanks for all your help!

Best, Preet Rajdeo

Marcin Zukowski · Accepted Answer · 2018-08-27 20:36:21Z

3

If your input is ALWAYS in this form (two elements in an array, with the same fields in the same element), you can combine PARSE_JSON function and the path access.

Just try this:

with input as (
  select parse_json(
    '{"custom": [ { "name": "addressIdNum", "valueNum": 12345678},  {"name": "cancelledDateAt", "valueAt": "2017-02-30T01:43:04.000Z" }] }') 
  as json) 
select json:custom[0].valueNum::integer, json:custom[1].valueAt::timestamp from input;
----------------------------------+-----------------------------------+
 JSON:CUSTOM[0].VALUENUM::INTEGER | JSON:CUSTOM[1].VALUEAT::TIMESTAMP |
----------------------------------+-----------------------------------+
 12345678                         | 2017-03-01 17:43:04               |
----------------------------------+-----------------------------------+

However, if the structure of your data might be different (e.g. elements in the array might be in a different order), it's probably best to write a JavaScript UDF in Snowflake to convert such messy data into something easier.

answered Aug 27, 2018 at 20:36

Marcin Zukowski

4,8391 gold badge25 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Preet Rajdeo Over a year ago

How do you write a Javascript UDF?

Marcin Zukowski Over a year ago

Did you look in the manual? docs.snowflake.net/manuals/sql-reference/udf-js.html

neeru0303 · Accepted Answer · 2024-12-11 07:00:49Z

0

This approach tackles elements in the array in different order without using UDFs. For more documentation refer to https://docs.snowflake.com/en/sql-reference/functions/filter

with input as (
  select parse_json(
    '{"custom": [ { "name": "addressIdNum", "valueNum": 12345678},  {"name": "cancelledDateAt", "valueAt": "2024-04-05 01:02:03" }] }')
  as json)
select
    json:custom as value,
    filter(
            value,
            a -> a:name::string = 'addressIdNum'
    )[0]:valueNum::integer as address_id_num,
    cast(
        filter(
            value,
            a -> a:name::string = 'cancelledDateAt'
    )[0]:valueAt as string)::timestamp as cancelled_date_at
from input;

answered Dec 11, 2024 at 7:00

neeru0303

1899 bronze badges

Collectives™ on Stack Overflow

Parse JSON using Snowflake SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related