Why does this simple WHERE clause doesn't return any data?

Question

I have a very simple table on BigQuery that I've created from another table. One of the fields is the user_email (string), which I've extracted from a JSON field with JSON_QUERY(json_field, "$.email").

I would like to get some specific emails from the table with a query that looks like this:

SELECT user_email
FROM my_table
WHERE user_email IN ("blabla@example.com", "blabla2@example.com"...)

The emails in the list from above DO exist, but I'm not getting any results from the query.

Any idea?

Answer 1

You should use JSON_VALUE instead of JSON_QUERY to extract a value as a BigQuery String. JSON QUERY returns a value as JSON-compatible String, not BigQuery String.

For example,

SELECT JSON_QUERY('{ "email": "blabla@example.com" }', '$.email') email;

Note that returned value is wrapped with double quotes.

But if you use JSON_VALUE,

SELECT JSON_VALUE('{ "email": "blabla@example.com" }', '$.email') email;

You can get a value without double quotes and be able to compare it with other BigQuery Strings IN ("blabla@example.com", "blabla2@example.com"...)