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

Question

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

Could you post an example of input data? – Sergey Geron May 26 '21 at 06:13 — Sergey Geron, May 26 '21 at 06:13

score 3 · Answer 1 · answered May 26 '21 at 06:12

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

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

1 Answers1