CODEWITHSUNDEEP
google-bigquerygoogle-cloud-platform
Fardream
Fardream

Reputation: 664

Big Query if over repeated record (with flatten)

This is regarding the solution given to the following question BigQuery SQL IF over repeated record: I've tried create a test table and tried the query given, and it didn't actually pick the people that lived in both New York and Chicago. Test data is below:

{"fullname": "John Smith", "citiesLived": [{"place": "newyork"}, {"place": "chicago"}, {"place": "seattle"}]}
{"fullname": "Adam Smith", "citiesLived": [{"place": "newyork"}, {"place": "chicago"}, {"place": "phil"}]}
{"fullname": "Adam Jefferson", "citiesLived": [{"place": "boston"}, {"place": "chicago"}, {"place": "seattle"}]}

and query is below:

SELECT
  *
FROM (
  SELECT
    fullname,
    IF (citiesLived.place == 'newyork', 1, 0) AS ny,
    IF (citiesLived.place == 'chicago', 1, 0) AS chi
  FROM (FLATTEN(tester.citiesLived, citiesLived))
  OMIT
    RECORD IF citiesLived.place = 'seattle')
WHERE
  ny == 1
  AND chi == 1

Upvotes: 3

Views: 748

Answers (2)

Dan Delorey
Dan Delorey

Reputation: 161

I believe that this would be a more complete rewrite of the original intended query:

SELECT
  *
FROM (
  SELECT
    fullname,
    SOME(citiesLived.place == 'newyork') WITHIN RECORD AS ny,
    SOME(citiesLived.place == 'chicago') WITHIN RECORD AS chi
  FROM tester.citiesLived
  OMIT
    RECORD IF SOME(citiesLived.place = 'seattle'))
WHERE
  ny == true
  AND chi == true

Upvotes: 0

Mosha Pasumansky
Mosha Pasumansky

Reputation: 13994

You don't need to do FLATTEN (in general FLATTEN is very rarely needed in BigQuery queries), just OMIT IF is enough:

SELECT fullname FROM tester.citiesLived
OMIT RECORD IF NOT (
  SOME(citiesLived.place = "newyork") AND
  SOME(citiesLived.place = "chicago"))

The condition of OMIT IF says that if SOME of the citiesLived is New York and SOME are Chicago - then it matches your criteria. But records where both is not true - should be omitted (hence the NOT predicate).

Upvotes: 2

Related Questions