JSON_EXTRACT return null value-CodePudding

I want to query the following JSON string in BigQuery:

'{"_id":"xxxxx","description":"stuff","userId":"1234}, {"_id":"xxxxx","description":"stuff","userId":"1234}'

I'm trying to run the following query but this is not working:

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

Why?

Ultimately I'm looking to query a JSON String stored as a string in cloud Storage.

CodePudding user response：

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