How to extract JSON value from VARCHAR column in Snowflake?

user_9124458 0
Viewed 322

I have a VARCHAR column storing JSON data. Here is one row:

    {
    "id": null,
    "ci": null,
    "mr": null,
    "meta_data":
    {
        "product":
        {
            "product_id": "123xyz",
            "sales":
            {
                "d_code": "UK",
                "c_code": "5814"
            },
            "amount":
            {
                "currency": "USD",
                "value": -1230
            },
            "entry_mode": "virtual",
            "transaction_date": "2020-01-01",
            "transaction_type": "purchase",
            "others":
            []
        }
    }
}

Example data:

WITH t1 AS (
SELECT '{"id":null,"ci":null,"mr":null,"meta_data":{"product":{"product_id":"123xyz","sales":{"d_code":"UK","c_code":"5814"},"amount":{"currency":"USD","value":-1230},"entry_mode":"virtual","transaction_date":"2020-01-01","transaction_type":"purchase","others":[]}}}'::varchar AS value
)

In Postgres, I do like this. How can I extract the following values below in Snowflake?

SELECT
    value,
    value -> 'meta_data' -> 'product' ->> 'product_id' AS product_id,
    value -> 'meta_data' -> 'product' -> 'sales' ->> 'd_code' AS d_code,
    value -> 'meta_data' -> 'product' -> 'sales' ->> 'c_code' AS c_code,
    value -> 'meta_data' -> 'product' -> 'amount' ->> 'currency' AS currency,
    value -> 'meta_data' -> 'product' ->> 'entry_mode' AS entry_mode,
    value -> 'meta_data' -> 'product' ->> 'transaction_type' AS transaction_type
FROM t1
snowflake-cloud-data-platform varchar json
2 Answers

It is possible with Snowflake too. The key point is usage of TRY_PARSE_JSON/PARSE_JSON:

PARSE_JSON

Interprets an input string as a JSON document, producing a VARIANT value.

WITH t1 AS (
SELECT '{"id":null,"ci":null,"mr":null,"meta_data":{"product":{"product_id":"123xyz","sales":{"d_code":"UK","c_code":"5814"},"amount":{"currency":"USD","value":-1230},"entry_mode":"virtual","transaction_date":"2020-01-01","transaction_type":"purchase","others":[]}}}'::varchar AS value
)
SELECT TRY_PARSE_JSON(t1.value) AS v
   ,v:meta_data:product:product_id::TEXT    AS product_id
   ,v:meta_data:product:sales:d_code::TEXT  AS d_code
   -- ...
FROM t1;

Or with another cte:

WITH t1 AS (
SELECT '{"id":null,"ci":null,"mr":null,"meta_data":{"product":{"product_id":"123xyz","sales":{"d_code":"UK","c_code":"5814"},"amount":{"currency":"USD","value":-1230},"entry_mode":"virtual","transaction_date":"2020-01-01","transaction_type":"purchase","others":[]}}}'::varchar AS value
), t1_cast AS (
   SELECT *,TRY_PARSE_JSON(t1.value) AS v
   FROM t1
)
SELECT 
    v:meta_data:product:product_id::TEXT    AS product_id
   ,v:meta_data:product:sales:d_code::TEXT  AS d_code
   -- ...
FROM t1_cast;

Output:

enter image description here

user_50708790

In Snowflake you use the VARIANT data type to store semi-structured data such as JSON. First, you should convert the VARCHAR string to VARIANT with the function PARSE_JSON, then you can query like this:

WITH t1 AS (
SELECT parse_json('{"id":null,"ci":null,"mr":null,"meta_data":{"product":{"product_id":"123xyz","sales":{"d_code":"UK","c_code":"5814"},"amount":{"currency":"USD","value":-1230},"entry_mode":"virtual","transaction_date":"2020-01-01","transaction_type":"purchase","others":[]}}}'::varchar) AS value
)
select value:meta_data:product:product_id as product_id,
    value:meta_data:product:sales:d_code as d_code,
    value:meta_data:product:sales:c_code AS c_code,
    value:meta_data:product:amount:currency AS currency,
    value:meta_data:product:entry_mode AS entry_mode,
    value:meta_data:product:transaction_type AS transaction_type
from t1;
user_165222240
Related