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

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 ("[email protected]", "[email protected]"...)

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

Any idea?

CodePudding user response：

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": "[email protected]" }', '$.email') email;

Note that returned value is wrapped with double quotes.

But if you use JSON_VALUE,

SELECT JSON_VALUE('{ "email": "[email protected]" }', '$.email') email;

You can get a value without double quotes and be able to compare it with other BigQuery Strings IN ("[email protected]", "[email protected]"...)