CODEWITHSUNDEEP
google-bigqueryjson-extract
user515766
user515766

Reputation: 369

In BigQuery, how to filter on json for rows with a condition on a subelement?

We use json_extract to extract fields for data in BigQuery I can easily filter by event_type, but how can I filter by title extracted from the JSON? for example: only titles that contain the word "facebook"

if I query by operation it works perfectly:

SELECT timestamp,
operation as event_type,
json_extract(data, '$.title') as title,
json_extract(data, '$.duration') as duration,
FROM `videos` 
WHERE (operation = "CREATE") ORDER BY timestamp ASC

Upvotes: 0

Views: 1514

Answers (1)

Sergey Geron
Sergey Geron

Reputation: 10222

Dose this work?

SELECT timestamp,
operation as event_type,
json_extract(data, '$.title') as title,
json_extract(data, '$.duration') as duration,
FROM `videos` 
WHERE operation = "CREATE"
  and json_value(data, '$.title') like '%facebook%'
ORDER BY timestamp ASC

Upvotes: 3

Related Questions