https://www.growthbook.io/ logo
Powered by
h

happy-lamp-67598

08/17/2023, 1:01 PM
Hello! We are trying to add a metric for transactions. I need the value for transaction_id which is a string in a JSON column. I am using this query but it is not working. Can you tell me what a SQL query for a JSON column should look like in Growthbook? Thanks a lot 🙂
Copy code
SELECT
  user_id,
  event_timestamp as timestamp
FROM
  `growthbook-ga4-396012`.`analytics_308238632`.`events_*`
WHERE
   event_name = 'purchase'  
  AND JSON_VALUE(ecommerce, '$.transaction_id') != NULL
@icy-zoo-15602 FYI
👍 1
h

helpful-application-7107

08/17/2023, 3:55 PM
Can you tell me what error you're getting?
It's very helpful to see the actual error message.
You may also consider selecting 
JSON_VALUE(ecommerce, '$.transaction_id')
in bigquery directly to make sure you see what it is returning.
if the transaction_id field is really 
NULL
then you may instead want to use:
Copy code
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
h

happy-lamp-67598

08/18/2023, 5:14 AM
Hey Luke! I get this error message. If I use your example it is the same.
h

helpful-application-7107

08/18/2023, 3:04 PM
Ok, it looks like your 
ecommerce
column is a STRUCT, not actually a JSON string. That's what the error message is saying.
So you probably just need to use 
ecommerce.transaction_id
instead of any of the JSON_VALUE extraction logic
Try:
Copy code
SELECT
  user_id,
  event_timestamp as timestamp
FROM
  `growthbook-ga4-396012`.`analytics_308238632`.`events_*`
WHERE
   event_name = 'purchase'  
  AND ecommerce.transaction_id IS NOT NULL
h

happy-lamp-67598

08/21/2023, 5:31 AM
That works. Thank you, Luke 🙂
Powered by