SELECT AS STRUCT/VALUES

Question

I am wondering what the possible use cases are for the SELECT AS STRUCT|VALUES modifier for GoogleSQL beyond the sort of textbook examples in the documentation.

The AS STRUCT reduces a non-scalar row into a scalar `STRUCT, so something like:

SELECT "David" AS name, 20 AS age

Could be converted into a subquery-able (scalar) item by doing something like:

SELECT  (
    SELECT AS STRUCT "David" AS name, 20 AS age
)

Or adding a bunch of debug info into a single (scalar) column by doing something like:

SELECT "David" AS name, 20 AS age, 
    (SELECT AS STRUCT 1 AS a, 2 AS b) debug
FROM (SELECT NULL)

But beyond that I can't see too much use, and I've never used it outside of trivial debugging queries myself. I was wondering if hopefully someone on the BigQuery team can explain:

• What are some actual examples when you use or find these two modifier keywords being used?
• Do any other SQL dialects use that construction (or a similar construction) and if not, why did GoogleSQL need to support it?

Answer 1

In following example the struct is used to left join tbl2 to tbl1. If there are several matching entries in tbl1, take only one (LIMIT 1). Thus the row size of tbl1 keeps constant. The SELECT AS STRUCT puts all rows of tbl2 into one structure.

WITH
  tbl1 as (SELECT * FROM UNNEST([1,2]) AS x),
  tbl2 AS (SELECT "a" AS name, 1 AS val
  UNION ALL SELECT "b", 2
  UNION ALL SELECT "bb", 2 )

SELECT *,
  (SELECT AS STRUCT *
  FROM (SELECT * FROM tbl2 WHERE x=val LIMIT 1)
  ) AS joined_table
FROM tbl1