CODEWITHSUNDEEP
jsonmultidimensional-arraynestedsnowflake-cloud-data-platformsnowflake-schema
KristiLuna
KristiLuna

Reputation: 1903

Snowflake - Querying Nested JSON

I need some help querying this JSON file I've ingested into a temp table in Snowflake. So, I've created a JSON_DATA variant column and plan to query and do a COPY INTO another table, but my query isn't working yet... I feel I'm close (possibly?)

JSON layout:

{
  "nextPage": "01",
  "page": "0",
  "status": "ok",
  "transactions": [
    {
      "id": "65985",
      "recordTp": "vendorbill",
      "values": {
        "account": [
          {
            "text": "14500 Deferred Expenses",
            "value": "249"
          }
        ],
        "account.number": "1450",
        "account.type": [
          {
            "text": "Deferred Expense",
            "value": "DeferExpense"
          }
        ],
        "amount": "51733",
        "classnohierarchy": [
          {
            "text": "901 Corporate",
            "value": "139"
          }
        ],
        "currency": [
          {
            "text": "Canadian Dollar",
            "value": "3"
          }
        ],
        "customer.altname": "V Sties expenses (Tor)",
        "customer.custate": "12/31/2019",
        "customer.custentient": "ada Inc.",
        "customer.custendate": "1/1/2019",
        "customer.entyid": "PR781",
        "departmentnohierarchy": [
          {
            "text": "8rity",
            "value": "37"
          }
        ],
        "fxamount": "689",
        "location": [
          {
            "text": "Othad Projects",
            "value": "48"
          }
        ],
        "postingperiod": [
          {
            "text": "Jan 2020",
            "value": "1"
          }
        ],
        "subsidiary.custrecord_region": [
          {
            "text": "CANADA",
            "value": "3"
          }
        ],
        "subsidiarynohierarchy": [
          {
            "text": "ada Inc.",
            "value": "25"
          }
        ]
      }
    },

I've been able to query the values that are not (deeply) nested but I need help getting, for example, the values from 'classnohierarchy', to get both the 'text' and 'value' I tried:

transactions.value:"values".classnohierarchy.text::string as class_txt,
transactions.value:"values".classnohierarchy.value::string as class_val,

but it's returning NULL values.

Below is my entire query:

SELECT 
JSON_DATA:status::string as connection_status,
transactions.value:id::string as id,
transactions.value:recordType::string as record_type,
transactions.value:"values"::variant as trans_val,
transactions.value:"values".account as acc,
transactions.value:"values".account.text as text,
transactions.value:"values".account.value as val,
transactions.value:"values"."account.number"::string as acc_num,
transactions.value:"values"."account.type".text::string as acc_type_txt,
transactions.value:"values"."account.type".value::string as acc_type_val,
transactions.value:"values".amount::string as amount,
**transactions.value:"values".classnohierarchy.text::string as class_txt,
transactions.value:"values".classnohierarchy.value::string as class_val,**
transactions.value:"values".currency.text::string as currency_text,
transactions.value:"values".currency.value::string as currency_val,
transactions.value:"values"."customer.altname"::string as customer_project_name,
transactions.value:"values"."customer.custate"::string as customer_end_date,
transactions.value:"values"."customer.custentient"::string as customer_end_client,
transactions.value:"values"."customer.custendate"::string as customer_start_date,
transactions.value:"values"."customer.entyid"::string as customer_project_id,
transactions.value:"values".departmentnohierarchy.text::string as department_name,
transactions.value:"values".departmentnohierarchy.value::string as department_value,
transactions.value:"values".fxamount::string as fx_amount,
transactions.value:"values".location.text::string as product_name,
transactions.value:"values".postingperiod.text::string as postingperiod,
transactions.value:"values".postingperiod.value::string as postingperiod,
transactions.value:"values"."subsidiary.custrecord_region".text::string as region_name,
transactions.value:"values"."subsidiary.custrecord_region".value::string as region_value,
transactions.value:"values".subsidiarynohierarchy.text::string as entity_name,
transactions.value:"values".subsidiarynohierarchy.value::string as entity_value,

FROM MY_TABLE,
LATERAL FLATTEN (JSON_DATA:transactions) as transactions

and here's a picture of whats showing in Snowflake:

SNOWFLAKE_SCREENSHOT

Upvotes: 1

Views: 7390

Answers (1)

Rajib Deb
Rajib Deb

Reputation: 1774

departmentnohierarchy is an array. you need to mention the index as below.

select *,transactions.VALUE:"values".departmentnohierarchy[0].value::text as department_name  
FROM jsont1,
LATERAL FLATTEN (JSON_DATA:transactions) as transactions

Upvotes: 1

Related Questions