how parse json in snowflake?

1
votes

I have below json data in snowflake datawarehouse. since rates have multiple day's data and date field doesn't have key and represented as value, how to flatten different date as separate row?

{
  "base": "USD",
  "end_at": "2020-08-17",
  "rates": {
    "2020-01-02": {
      "AUD": 1.4300008934,
      "BGN": 1.7473420888,
      "BRL": 4.0087554722,
      "CAD": 1.299830251,
      "CHF": 0.9706959707
    },
    "2020-01-03": {
      "AUD": 1.4381447923,
      "BGN": 1.7545527945,
      "BRL": 4.0612720911,
      "CAD": 1.2981968243,
      "CHF": 0.9724589576
    },
..
..
..,
  "start_at": "2020-01-01"
}
2
jsonsnowflake-cloud-data-platform

2 Answers

1
votes

try using flatten with RECURSIVE activated. I've tried the following method to validate using your JSON structure and was able to get a valid result.

    //Store the JSON in VARIANT columns
    create temporary table JSON_SNIPPIT(
    JSON_RAW VARIANT
    )
    AS
    SELECT PARSE_JSON('
    {
      "base": "USD",
      "end_at": "2020-08-17",
      "rates": {
        "2020-01-02": {
          "AUD": 1.4300008934,
          "BGN": 1.7473420888,
          "BRL": 4.0087554722,
          "CAD": 1.299830251,
          "CHF": 0.9706959707
        },
        "2020-01-03": {
          "AUD": 1.4381447923,
          "BGN": 1.7545527945,
          "BRL": 4.0612720911,
          "CAD": 1.2981968243,
          "CHF": 0.9724589576
        },},
    "start_at": "2020-01-01"
    }');
    // This query to list all fields to check what to customise to your need
    Select * FROM JSON_SNIPPIT,LATERAL FLATTEN( INPUT => JSON_RAW:rates, RECURSIVE => TRUE);

//  Here I used this SQL to list all the currencies ordered by day, hope this what you want, enjoy!
        SELECT
          SUBSTRING(PATH,3,10) AS DAY_DATE,
          KEY AS CURRENCY,
          value::number(15,10) as DAY_RATES
          FROM JSON_SNIPPIT
          ,LATERAL FLATTEN( INPUT => JSON_RAW:rates, RECURSIVE => TRUE)
          WHERE KEY in ('AUD','BGN','BRL','CAD','CHF')
          ORDER BY 2,1;

Result:-

    DAY_DATE    CURRENCY    DAY_RATES
    2020-01-02  AUD         1.4300008934
    2020-01-03  AUD         1.4381447923
    2020-01-02  BGN         1.7473420888
    2020-01-03  BGN         1.7545527945
    2020-01-02  BRL         4.0087554722
    2020-01-03  BRL         4.0612720911
    2020-01-02  CAD         1.2998302510
    2020-01-03  CAD         1.2981968243
    2020-01-02  CHF         0.9706959707
    2020-01-03  CHF         0.9724589576
0
votes

If you only want ONE ROW PER DAY, then should we assume that you want all of the currency values left in a dictionary (variant)? If so, then perhaps this will suffice:

WITH CTE_JSON AS (
  SELECT PARSE_JSON($1) AS MY_DICT
    FROM VALUES ($$
{
  "base": "USD",
  "end_at": "2020-08-17",
  "rates": {
    "2020-01-02": {
      "AUD": 1.4300008934,
      "BGN": 1.7473420888,
      "BRL": 4.0087554722,
      "CAD": 1.299830251,
      "CHF": 0.9706959707
    },
    "2020-01-03": {
      "AUD": 1.4381447923,
      "BGN": 1.7545527945,
      "BRL": 4.0612720911,
      "CAD": 1.2981968243,
      "CHF": 0.9724589576
    }
  },
  "start_at": "2020-01-01"
}
$$)
)
SELECT F.KEY::DATE AS MY_DATE
      ,F.VALUE AS CURRENCY_DICT
  FROM CTE_JSON C
      ,LATERAL FLATTEN(C.MY_DICT:"rates") F
 ORDER BY 1
;

If you are seeking a different result set, then please specify exactly what that needs to be.