happy-lamp-67598
08/17/2023, 1:01 PMSELECT
user_id,
event_timestamp as timestamp
FROM
`growthbook-ga4-396012`.`analytics_308238632`.`events_*`
WHERE
event_name = 'purchase'
AND JSON_VALUE(ecommerce, '$.transaction_id') != NULL
helpful-application-7107
08/17/2023, 3:55 PMJSON_VALUE(ecommerce, '$.transaction_id')
in bigquery directly to make sure you see what it is returning.NULL
then you may instead want to use:
SELECT
user_id,
event_timestamp as timestamp
FROM
`growthbook-ga4-396012`.`analytics_308238632`.`events_*`
WHERE
event_name = 'purchase'
AND JSON_VALUE(ecommerce, '$.transaction_id') IS NOT NULL
because you may have gotten the following error: Operands of != cannot be literal NULL
happy-lamp-67598
08/18/2023, 5:14 AMhelpful-application-7107
08/18/2023, 3:04 PMecommerce
column is a STRUCT, not actually a JSON string. That's what the error message is saying.ecommerce.transaction_id
instead of any of the JSON_VALUE extraction logicSELECT
user_id,
event_timestamp as timestamp
FROM
`growthbook-ga4-396012`.`analytics_308238632`.`events_*`
WHERE
event_name = 'purchase'
AND ecommerce.transaction_id IS NOT NULL
happy-lamp-67598
08/21/2023, 5:31 AM