CODEWITHSUNDEEP
google-bigquery
Timogavk
Timogavk

Reputation: 869

How to compare column in one table with array from another table in BigQuery?

Just continue from the answer for my previous question.

I want to get all values from table b (in rows) if there is any difference between values in arrays from table a by same ids

WITH a as (SELECT 1 as id, ['123', 'abc', '456', 'qaz', 'uqw'] as value
UNION ALL SELECT 2, ['123', 'wer', 'thg', '10', '200']
UNION ALL SELECT 3, ['200']
UNION ALL SELECT 4, null
UNION ALL SELECT 5, ['140']),
b as (SELECT 1 as id, '123' as value
      UNION ALL SELECT 1, 'abc'
      UNION ALL SELECT 1, '456'
      UNION ALL SELECT 1, 'qaz'
      UNION ALL SELECT 1, 'uqw'
      UNION ALL SELECT 2, '123'
      UNION ALL SELECT 2, 'wer'
      UNION ALL SELECT 2, '10'
      UNION ALL SELECT 3, null
      UNION ALL SELECT 4, 'wer'
      UNION ALL SELECT 4, '234'
      UNION ALL SELECT 5, '140'
      UNION ALL SELECT 5, '121'

)
SELECT * EXCEPT(flag)
FROM (
  SELECT b.*, COUNTIF(b.value IS NULL) OVER(PARTITION BY id) flag
  FROM a LEFT JOIN a.value 
  FULL OUTER JOIN b
  USING(id, value)
)
WHERE flag > 0
AND NOT id IS NULL

It works well for all ids except 5. In my case I need to return all values if there is any difference.

In example array with id 5 from table a has only one value is '140' while there are two rows with values by id 5 from table b. So in this case all values by id 5 from table b also must appear in expected output

How need to modify this query to get what I want?

UPDATED

Seems like it works for me. But I can not be sure for 100%

SELECT * EXCEPT(flag)
FROM (
  SELECT b.*, COUNTIF((b.value IS NULL AND a.value IS NOT NULL) OR (b.value IS NOT NULL AND a.value IS NULL)) OVER(PARTITION BY id) flag
  FROM a LEFT JOIN a.value 
  FULL OUTER JOIN b
  USING(id, value)
)
WHERE flag > 0
AND NOT id IS NULL

Upvotes: 0

Views: 611

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

#standardSQL
SELECT * 
FROM table_b 
WHERE id IN (
  SELECT id FROM table_a a
  JOIN table_b b USING(id)
  GROUP BY id
  HAVING STRING_AGG(IFNULL(b.value, 'NULL') ORDER BY b.value) !=
    IFNULL(ANY_VALUE((SELECT STRING_AGG(IFNULL(value, 'NULL') ORDER BY value) FROM a.value)), 'NULL')
)

Upvotes: 1

Related Questions