JSON_EXTRACT return null value

Solution 1:

In your example there is some quoting issues:

• the single quotes that are around raw_json in your query make BQ think you are querying the 'userId' field of the string 'raw_json'. This single string does not have such field (it isn't even a properly formatted json, I'm surprised no error is raised), so the result is null.

The following works:

WITH test AS (
  SELECT '{"_id":"xxxxx","description":"stuff","userId":"1234"}' raw_json 
UNION ALL
  SELECT '{"_id":"xxxxx","description":"stuff","userId":"1234"}'
)
select JSON_EXTRACT(raw_json, '$.userId') AS json_extract,
from test

Note that apparently the recommended way is to use JSON_VALUE:

WITH test AS (
  SELECT '{"_id":"xxxxx","description":"stuff","userId":"1234"}' raw_json 
UNION ALL
  SELECT '{"_id":"xxxxx","description":"stuff","userId":"1234"}'
)
select JSON_VALUE(raw_json, '$.userId') AS json_extract,
from test