JSON query with Snowflake

user_9347738 0
Viewed 410

Hello I have the below json field (addressbooklist) in which im trying to extract addr1 however my code results are NULL Can someone help?

Query-

select 
addressbooklist:addressbook.addressbookAddress.addr1 as test,
from customer;

JSON Field

{
  "addressbook": [
    {
      "addressbookAddress": {
        "addr1": "701 N. Brand Boulevard",
        "addr2": null,
        "addr3": null,
        "addrPhone": null,
        "addrText": "Executive Software International<br>701 N. Brand Boulevard<br>Glendale CA 912031242<br>United States",
        "addressee": "Executive Software International",
        "attention": null,
        "city": "Glendale",
        "country": {
          "value": "_unitedStates"
        },
        "customFieldList": null,
        "internalId": null,
        "nullFieldList": null,
        "override": false,
        "state": "CA",
        "zip": "912031242"
      },
      "defaultBilling": false,
      "defaultShipping": true,
      "internalId": "1587207",
      "isResidential": false,
      "label": "701 N. Brand Boulevard"
    },
    {
      "addressbookAddress": {
        "addr1": "701 N. Brand Boulevard",
        "addr2": null,
        "addr3": null,
        "addrPhone": null,
        "addrText": "Executive Software International<br>701 N. Brand Boulevard<br>Glendale CA 912031242<br>United States",
        "addressee": "Executive Software International",
        "attention": null,
        "city": "Glendale",
        "country": {
          "value": "_unitedStates"
        },
        "customFieldList": null,
        "internalId": null,
        "nullFieldList": null,
        "override": false,
        "state": "CA",
        "zip": "912031242"
      },
      "defaultBilling": true,
      "defaultShipping": false,
      "internalId": "1587208",
      "isResidential": false,
      "label": "701 N. Brand Boulevard"
    }
  ],
  "replaceAll": false
}
snowflake-sql sql json
3 Answers

Your json document addressbook element is an array, not a plain property. You can reference a specific element in an array with [0] notation (0=first element, 1=second,...). Your query would look like this:

select addressbooklist:addressbook[0].addressbookAddress.addr1 as test from customer;

If you want to have all of the addressbook items in the result, you can flatten the json structure with this kind of query:

select ab.value:addressbookAddress.addr1 as test
from customer
, lateral flatten(input => addressbooklist:addressbook) ab;

Result:

TEST
"701 N. Brand Boulevard"
"701 N. Brand Boulevard"

More info on Snowflake json syntax for arrays can be found here:

user_18182790

It would help to know which language you want to use. I just have to do this with mySQL. Something like this may help:

select yourField->>'$."addressbookAddress"[0].addr1' as address from yourTable where 1

user_101343490

The JSON object you refered to has the following structure:

Array[Object1{key1: value, key2: value}, Object2{key1: value, key2: value}]

Object1 is the first item of your array with the index 0, Object2 has index 1 and so on.

You access an item of an array like Array[0] and you can reach a value inside an object like Object1.key1. In this example you would reach out to key1 like Array[0]Object1.key1.

In your case you can access addr1 in you first object like this:

addressbook[0].addressbookAddress.addr1
user_131293530
Related