How to return an array of structs from a struct of arrays in Standard SQL?

Question

I have a non-repeated record column on my table that I want to access. On this record, there are several repeated values.

So it is a RECORD, like so:

STRUCT<item ARRAY<STRING> unit_cost ARRAY<INT64> quantity ARRAY<INT64>> as costs

Eg. the data might represent:

item ['cheese', 'ham', 'salad']
unit_cost [2, 5, 8]
quantity [1, 2, 1]

So I want to return this as a nicer data structure, an array of structs:

[
  {'item': 'cheese', 'unit_cost': 2, 'quantity': 1}, 
  {'item': 'ham', 'unit_cost': 5, 'quantity': 2}
  {'item': 'salad', 'unit_cost': 8, 'quantity': 1}
]

I tried:

SELECT ARRAY_AGG(costs)

but it results in

            [
              {
                "item": ['cheese', 'ham', 'salad'],
                "unit_cost": [2, 5, 8],
                "quantity": [1, 2, 1]
              }
            ]

which is not what I had expected it to return.

Is it possible to go from a STRUCT of multiple ARRAY to an ARRAY of multiple STRUCT using some clever use of Standard SQL here?

score 6 · Answer 1 · answered Oct 04 '19 at 19:05

Below is for BigQuery Standard SQL

#standardSQL
SELECT 
  ARRAY(
    SELECT AS STRUCT item, unit_cost, quantity
    FROM UNNEST(costs.item) item WITH OFFSET
    LEFT JOIN UNNEST(costs.unit_cost) unit_cost WITH OFFSET USING(OFFSET)
    LEFT JOIN UNNEST(costs.quantity) quantity WITH OFFSET USING(OFFSET)
  ) AS costs
FROM `project.dataset.table`

if to apply to sample data from your question - result is (in JSON View)

[
  {
    "costs": [
      {
        "item": "cheese",
        "unit_cost": "2",
        "quantity": "1"
      },
      {
        "item": "ham",
        "unit_cost": "5",
        "quantity": "2"
      },
      {
        "item": "salad",
        "unit_cost": "8",
        "quantity": "1"
      }
    ]
  }
]

Yun Zhang · Answer 2 · 2019-10-04T20:19:13.367

You can use below query:

with data as (
select STRUCT<item ARRAY<STRING>, unit_cost ARRAY<INT64>, quantity ARRAY<INT64>>
  (['cheese', 'ham', 'salad'], [2, 5, 8], [1, 2, 1]) entry
union all
select (['othercheese', 'otherham', 'othersalad'], [3, 8, 10], [11, 22, 11])
union all
select (['othercheese', 'otherham', 'othersalad'], [3, 8, 10], [11, 22, 11])
) 
SELECT ARRAY_AGG(STRUCT(item, unit_cost, quantity))
FROM data, UNNEST(entry.item) item WITH OFFSET
    LEFT JOIN UNNEST(entry.unit_cost) unit_cost WITH OFFSET USING(OFFSET)
    LEFT JOIN UNNEST(entry.quantity) quantity WITH OFFSET USING(OFFSET)

Output

[
  {
    "f0_": [
      {
        "item": "cheese",
        "unit_cost": "2",
        "quantity": "1"
      },
      {
        "item": "ham",
        "unit_cost": "5",
        "quantity": "2"
      },
      {
        "item": "salad",
        "unit_cost": "8",
        "quantity": "1"
      },
      {
        "item": "othercheese",
        "unit_cost": "3",
        "quantity": "11"
      },
      {
        "item": "otherham",
        "unit_cost": "8",
        "quantity": "22"
      },
      {
        "item": "othersalad",
        "unit_cost": "10",
        "quantity": "11"
      },
      {
        "item": "othercheese",
        "unit_cost": "3",
        "quantity": "11"
      },
      {
        "item": "otherham",
        "unit_cost": "8",
        "quantity": "22"
      },
      {
        "item": "othersalad",
        "unit_cost": "10",
        "quantity": "11"
      }
    ]
  }
]

How to return an array of structs from a struct of arrays in Standard SQL?

2 Answers2