Implicitly creating a struct

Question

In the following query:

select t, 1 from (select 1 as a, 2 as b) t

It produces a column of struct type: {"a": 1, "b": 2} for the column t. Is this the expected behavior, and if so (and I'm almost 100% sure it is), where is that behavior documented in BigQuery or in the SQL standard?

Another example:

select t as x, t.*, 1 as y from (select 1 as a, 2 as b) t

I've been reading the SQL:2023 specification for 30 minutes now without finding anything like this. (Very time-consuming document to read.) Anyway, "following" this question. — jarlh
– jarlh, Commented Apr 2, 2024 at 20:45
@jarlh -- thanks. Where did you find the standard from? Or did you purchase it from the ISO website? — David542
– David542, Commented Apr 2, 2024 at 21:26

Mel · Accepted Answer · 2024-04-15 16:46:17Z

1

This is definitely the expected behavior in BigQuery. While the documentation might not show examples exactly like yours, the underlying concept aligns perfectly. I ran your query using BigQuery's sample structure and got the same results.

Query 1:

WITH t AS (select 1 as a, 2 as b)
SELECT t, 1 FROM t;

Query 2:

WITH t AS (select 1 as a, 2 as b)
SELECT t as x, t.*, 1 as y FROM t;

The same is observed in a couple of related posts.

answered Apr 15, 2024 at 16:46

community wiki

Mel

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Implicitly creating a struct

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related