我想在 BigQuery 中查詢以下 JSON 字串：
'{"_id":"xxxxx","description":"stuff","userId":"1234}, {"_id":"xxxxx","description":"stuff","userId":"1234}'
我正在嘗試運行以下查詢，但這不起作用：
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
為什么？
最終，我希望查詢在云存盤中存盤為字串的 JSON 字串。
uj5u.com熱心網友回復：
在您的示例中存在一些參考問題：
- 查詢中 raw_json 周圍的單引號使 BQ 認為您正在查詢字串“raw_json”的“userId”欄位。這個單個字串沒有這樣的欄位（它甚至不是格式正確的 json，我很驚訝沒有引發錯誤），所以結果為空。
以下作品：
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
請注意，顯然推薦的方法是使用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
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/411057.html
標籤：
下一篇：如何回傳唯一記錄