How to read nested JSON data using UNNEST in BigQuery Table

To read data from a JSON field in Bigquery you will need to use the UNNEST Command. For example, If you have a table in Bigquery which has JSON data like below.

{
    "Customer_id": "1",
    "transactions": [
      {
        "transaction_id": "4529b00ed3315ff854081",
      },
      {
        "transaction_id": "838b03a6f741c844e2207",
      }
    ]
}

Say, If you want to extract the transaction_id from this table.

SELECT
  tr.transaction_id
FROM
  `bigquery-public-data.bitcoin_blockchain.blocks`,
  UNNEST(transactions) AS tr
LIMIT 100

In this example, we are making use of the UNNEST Operator in order to query for specific fields inside an array

mCollins

posted on 01 Mar 19