查询嵌套的可重复记录(Querying Nested Repeatable Records)

查询嵌套的可重复记录(Querying Nested Repeatable Records)

如果我有一个模式，其中表具有可重复记录'age'，并且该可重复记录的结构是：

{年龄：28}

是否可以发出查询来搜索行的'age'记录包含“age = 28 AND age = 56”的所有行。

所以在这个数据中：

[id = 1，年龄：[{年龄：28}，{年龄：56}，{年龄：62}]] [id = 2，年龄：[{年龄：28}]] [id = 3，年龄：[ {年龄：28}，{年龄：56}]]

只有第1和第3行回来了吗？

提前致谢。

If I have a schema where a table has a repeatable record 'age', and the structure of that repeatable record is:

{age: 28}

Is it possible to issue a query to search for all rows where the row's 'age' record contains "age = 28 AND age = 56".

So in this data:

[id=1, age: [{age: 28},{age: 56},{age: 62}] ] [id=2, age: [{age: 28}] ] [id=3, age: [{age: 28},{age: 56}] ]

only rows 1 and 3 come back?

Thanks in advance.

最满意答案

假设记录中没有重复的年龄 和YourTable如下所示

id age 1 28 56 62 2 28 3 28 56

for BigQuery Legacy SQL

SELECT * FROM YourTable OMIT RECORD IF SUM(age=28 OR age=56) <> 2

要么

SELECT *, SUM(age=28 OR age=56) WITHIN RECORD AS qualify FROM YourTable HAVING qualify = 2

for BigQuery Standard SQL（请参阅启用标准SQL ）

SELECT * FROM YourTable WHERE (SELECT COUNT(1) FROM UNNEST(age) AS a WHERE a = 28 OR a = 56 ) = 2

assuming there are no duplicate ages within the record and YourTable looks as below

id age 1 28 56 62 2 28 3 28 56

for BigQuery Legacy SQL

SELECT * FROM YourTable OMIT RECORD IF SUM(age=28 OR age=56) <> 2

or

SELECT *, SUM(age=28 OR age=56) WITHIN RECORD AS qualify FROM YourTable HAVING qualify = 2

for BigQuery Standard SQL (see Enabling Standard SQL)

SELECT * FROM YourTable WHERE (SELECT COUNT(1) FROM UNNEST(age) AS a WHERE a = 28 OR a = 56 ) = 2