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.

\n\n

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

\n\n

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

\n\n

SELECT\n  tr.transaction_id\nFROM\n  `bigquery-public-data.bitcoin_blockchain.blocks`,\n  UNNEST(transactions) AS tr\nLIMIT 100\n

\n\n

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

mCollins

posted on

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