https://www.growthbook.io/ logo
Join Slack
Powered by
Hello! We are trying to add a metric for transacti...
# ask-questions
h
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
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
Hey Luke! I get this error message. If I use your example it is the same.
h
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
That works. Thank you, Luke 🙂
67 Views
Open in Slack
PreviousNext
Powered by