Google Bigquery, WHERE clause based on JSON item

Question

I've got a bigquery import from a firestore database where I want to query on a particular field from a document. This was populated via the firestore-bigquery extension and the document data is stored as a JSON string.

I'm trying to use a WHERE clause in my query that uses one of the fields from the JSON data. However this doesn't seem to work.

My query is as follows:

SELECT json_extract(data,'$.title') as title,p
FROM `table`  
left join unnest(json_extract_array(data, '$.tags')) as p
where json_extract(data,'$.title') = 'technology'

data is the JSON object and title is an attribute of all of the items. The above query will run but yield 'no results' (There are definitely results there for the title in question as they appear in the table preview).

I've tried using WHERE title = 'technology' as well but this returns an error that title is an unrecognized field (hence the json_extract).

From my research this should work as a standard SQL JSON query but doesn't seem to work on Bigquery. Does anyone know of a way around this?

All I can think of is if I put the results in another table, but I don't know if that's a workable solution as the data is updated via the extension on an update, so I would need to constantly refresh my second table as well.

Edit I'm wondering if configuring a view would help with this? Though ultimately I would like to query this based on different parameters and the docs here https://cloud.google.com/bigquery/docs/views suggest you can't reference query parameters in a view

TommyBs TommyBs · Accepted Answer · 2020-12-21T17:26:13

I've since managed to work this out, and will share the solution for anyone else with the same problem.

The solution was to use JSON_VALUE in the WHERE clause instead e.g:

where JSON_VALUE(data,'$.title') = 'technology';

I'm still not sure if this is the best way to do this in terms of performance and cost so I will wait to see if anyone else leaves a better answer.

Google Bigquery, WHERE clause based on JSON item

1 Answers