CODEWITHSUNDEEP
google-bigquery
Jason Berryman
Jason Berryman

Reputation: 4898

BigQuery query from a nested field

I have a table with a nested field externalIds.value

I want to run a query where 2 entries match values in this field. Here is the query I have, which doesn't return any data.

SELECT keyField, field2, example.field1, example.field2,
externalIds.type, externalIds.value FROM (FLATTEN([dataset1.table1], externalIds))
WHERE
externalIds.value = '157' AND
externalIds.value = 'Some test data'
;

If I run this query with only 1 WHERE clause (externalIds.value = '157') and then run a query WHERE keyField = "The value returned from the previous query", then I get two lines. One showing the externalIds.value as '157' and another result where it is 'Some test data'.

I'm not interested in displaying both values in the result. My priority is to get the keyField WHERE the .value is '157' AND 'Some test data'

Upvotes: 1

Views: 247

Answers (1)

Mosha Pasumansky
Mosha Pasumansky

Reputation: 13994

Maybe something like that:

SELECT keyField, field2, example.field1, example.field2,
externalIds.type, externalIds.value FROM [dataset1.table1]
OMIT RECORD IF NOT(
  SOME(externalIds.value = '157') AND
  SOME(externalIds.value = 'Some test data'))

Upvotes: 1

Related Questions