How to convert an array extracted from a json string field to a bigquery Repeated field?

user_659171 0
Viewed 16745

We have loaded json blobs in a String field in a Bigquery table. I need to create a view (using standard sql)over the table that would extract the array field as a bigquery array/repeated field of "RECORD" type (which itself includes a repeated field).

Here is a sample record (json_blob): 

{"order_id":"123456","customer_id":"2abcd", "items":[{"line":"1","ref_ids":["66b56e60","9e7ca2b7"],"sku":"1111","amount":40 },{"line":"2","ref_ids":["7777h0","8888j0"],"sku":"2222","amount":10 }]}

I am hoping to end up with a view that has the following layout:

[
{
    "name": "order_id",
    "type": "STRING",
    "mode": "NULLABLE"
},
{
    "mode": "NULLABLE",
    "name": "customer_id",
    "type": "STRING"
},
{
    "mode": "REPEATED",
    "name": "items",
    "type": "RECORD",
    "fields": [
        {
            "mode": "NULLABLE",
            "name": "line",
            "type": "STRING"
        },
        {
            "mode": "REPEATED",
            "name": "ref_ids",
            "type": "STRING"
        },
        {
            "mode": "NULLABLE",
            "name": "sku",
            "type": "STRING"
        },
        {
            "mode": "NULLABLE",
            "name": "amount",
            "type": "INTEGER"
        }
    ]
}
]

Json_extract(json_blob, '$.items') extracts the items parts, but how do I convert that to a bigquery array of type "RECORD" which then can be processed like normal bigquery array/repeated of STRUCT?

Appreciate any help.

google-bigquery
3 Answers

As of 1st May 2020, JSON_EXTRACT_ARRAY function has been added, and can be used to retrieve array from json.

#standardSQL
WITH `yourTable` AS (
  SELECT '{"order_id":"123456","customer_id":"2abcd", "items":[{"line":"1","ref_ids":["66b56e60","9e7ca2b7"],"sku":"1111","amount":40 },{"line":"2","ref_ids":["7777h0","8888j0"],"sku":"2222","amount":10 }]}' AS json_blob 
)
SELECT
  json_extract_scalar(json_blob,'$.order_id') AS order_id,
  json_extract_scalar(json_blob,'$.customer_id') AS customer_id,
  ARRAY(
  SELECT
    STRUCT(json_extract_scalar(split_items,'$.line') AS line,
          ARRAY(SELECT json_extract_scalar(ref_element,'$') FROM UNNEST(json_extract_array(split_items, '$.ref_ids')) ref_element) AS ref_ids,
          json_extract_scalar(split_items,'$.sku') AS sku,
          json_extract_scalar(split_items,'$.amount') AS amount 
      )
    FROM UNNEST(json_extract_array(json_blob,'$.items')) split_items 
  ) AS items
FROM
  `yourTable`

Returns:

enter image description here

To get only the type query would be:

#standardSQL
WITH `yourTable` AS (
  SELECT '{ "firstName": "John", "lastName" : "doe", "age"      : 26, "address"  : {     "streetAddress": "naist street",     "city"         : "Nara",     "postalCode"   : "630-0192" }, "phoneNumbers": [     {       "type"  : "iPhone",       "number": "0123-4567-8888"     },     {       "type"  : "home",       "number": "0123-4567-8910"     } ]}' AS json_blob 
)
  SELECT
    json_extract_scalar(split_items,'$.type') AS type FROM `yourTable`, UNNEST(json_extract_array(json_blob,'$.phoneNumbers')) split_items

returns:

enter image description here

user_2437820
Related