CODEWITHSUNDEEP
sqlgoogle-bigquery
Jessica
Jessica

Reputation: 3

How do I write a query to reference two keys and two values?

I am trying to write a query in Google BigQuery that pulls two keys and two values. The query should be: count distinct psuedo user IDs from one table where event_params.key = result and event_params.key = confirmation number (and is not null), and event_params.value.string_value = success. This has already been unnested. I'm SUPER new to SQL, so please dumb down any answers.

SELECT
*
FROM
  `table_name`,
  UNNEST(event_params) AS params
WHERE
  (stream_id = '1168190076'
    OR stream_id = '1168201031')
  AND params.key = 'result'
  AND params.value.string_value IN ('success',
    'SUCCESS')
    AND params.key = 'confirmationNumber' NOT NULL

I keep getting errors, and when I don't get errors, my numbers are off by a lot! I'm not sure where to go next.

Upvotes: 0

Views: 433

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173038

Below is for BigQuery Standard SQL 

#standardSQL
SELECT *
FROM `project.dataset.table`
WHERE stream_id IN ('1168190076', '1168201031') 
AND 2 = (
  SELECT COUNT(1)
  FROM UNNEST(event_params) param
  WHERE (
    param.key = 'result' AND 
    LOWER(param.value.string_value) = 'success'
  ) OR (
    param.key = 'confirmationNumber' AND 
    NOT param.value.string_value IS NULL
  )
)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270081

I suspect that you want something more like this:

SELECT t.*
FROM `table_name`t
  UNNEST(event_params) AS params
WHERE t.stream_id IN ('1168190076', '1168201031') AND
      EXISTS (SELECT 1
              FROM UNNEST(t.event_params) p
              WHERE p.key = 'result' AND
                    p.value.string_value IN ('success', 'SUCCESS')
             ) AND
      EXISTS (SELECT 1
              FROM UNNEST(t.event_params) p
              WHERE p.key = 'confirmationNumber'
             );

That is, test each parameter independently. You don't need to unnest the result for the result set -- unless you really want to, of course.

I don't know what the lingering NOT NULL is for in your query, so I'm ignoring it. You might want to check the value, however.

Upvotes: 0

Related Questions