json extract string traits that are nested

March 8, 2023

This is a part of the json statement.

{
  "customer": {
    "spendingHold": false,
    "createdAt": "2023-03-08T00:00:00.000Z",
    "addresses": [
      {
        "country": "USA",
        "preferences": {
          "contact": {
            "allowed": {
              "mail": true
            }
          }
        },
        "city": "Place",
        "postalCode": "11111",
        "street1": "123 Circle",
        "street2": null,
        "id": "1234567890",
        "type": "home",
        "region": "ST",
        "primary": true
      }
    ],
      "contact": {
        "allowed": {
          "times": null,
          "transactional": true
        }
      }
    },
    "creationSource": "created",
}
}

I can extract customer and creationSource with

SELECT
JSON_EXTRACT_SCALAR(json, "$.customer.spendingHold") AS spending_hold,
FROM dataset

But I am having difficulty trying to get traits like addresses and those in it like country, preferences, contact, etc.

My desired output should look like:

spendingHold	createdAt	country	mail	city
false	2023-03-08T00:00:00.000Z	USA	true	Place

>Solution :

You can consider below query

WITH dataset AS (
  SELECT '''
  -- put your json string here.
  ''' json
)
SELECT JSON_VALUE(json, "$.customer.spendingHold") AS spending_hold,
       JSON_VALUE(json, "$.customer.createdAt") AS createdAt,
       JSON_VALUE(address, '$.country') AS country,
       JSON_VALUE(address, '$.preferences.contact.allowed.mail') AS mail,
       JSON_VALUE(address, '$.city') AS city
  FROM dataset, UNNEST(JSON_QUERY_ARRAY(json, '$.customer.addresses')) address;

Query results