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

npack

posted on 01 Mar 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds