Bigquery nested table resulting out different results for similar queries-CodePudding

I have Bigquery table with Record type 'trigger_prop' column and here are two ways using which I am querying but it is resulting out different output:

First Query:

SELECT b.* FROM dataset.table a, unnest(trigger_prop) as b
where b.number=425600;

Second Query:

SELECT a.trigger_prop FROM dataset.table a, unnest(trigger_prop) as b
where b.number=425600;

'Query1 is resulting out 1 record', while 'query2 is resulting out 2 records' from table which are actual records. These both queries are doing same thing but resulting out different output. First query is returning less record while second query is returning actual records. Can someone please help me in understanding the difference in both queries?

CodePudding user response：

They are not doing the same thing, the difference is the SELECT a.trigger_prop vs SELECT b.* which are different tables.
The first query selects the unnested translation of trigger_propfrom table b which "unfolds" the array and keeps the single array item with number=425600.
The second query selects the raw trigger_prop field from the source table and returns a single row, but this row has multiple array items in it so it looks like multiple rows because of the array in trigger_prop.

To demonstrate I ran the following query, note the LIMIT 1 clause:

WITH
  TABLE AS (
  SELECT
    ARRAY<STRUCT<number INTEGER,
    name STRING>>[(1,
      "me"),
    (2,
      "you")] AS trigger_prop)
SELECT
  a.trigger_prop
FROM
  TABLE AS a,
  UNNEST(trigger_prop) AS b
WHERE
  b.number=1
LIMIT 1

This gives the following result, note how it's a single row which looks like multiple rows.