01 Mar 19 ·  mCollins in bigquery ·        Bookmark ·  Report ·  More actions.. Lock comments ·  Pin thread

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.

  UNNEST(transactions) AS tr

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


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