BigQuery -如何访问WHERE中的变量

Question

我是BigQuery (GoogleSQL)世界的新手，我尝试在表(链接到Firestore)中查询值。

在使用firestore时，我的表具有以下结构

[{
"document_name": "pathDocument",
"document_id": "myDocumentID",
"timestamp": "2023-04-01 08:23:11.213755 UTC",
"event_id": "myeventID",
"operation": "CREATE",
"data": "{"Code":"0000","Country":"China","Currency":"CNY","Online":"true",....}",
"old_data": null
}, ....]

我的目标是让代码在"data"通过对"数据"中包含的不同变量进行筛选，例如Country=China AND Online=true

我已经成功做到了，但我不知道这是否是正确的方式(或优化)，因为我在哪里做操作:

SELECT 
JSON_EXTRACT(data, '$.Code') as Code,
FROM `****.***_raw_latest` 
WHERE CAST(JSON_EXTRACT(data, '$.Country') AS STRING) = 'China'
AND
WHERE CAST(JSON_EXTRACT(data, '$.Online') AS BOOL) = TRUE
LIMIT 50;

备注:

• 我使用CAST，因为所有数据都设置为"data">
中的字符串
• 我可以有多个操作，如>， <…

有没有更合适的方法?

例如，我尝试了以下操作，但是变量Country不可访问

SELECT 
JSON_EXTRACT(data, '$.Code') as Code,
JSON_EXTRACT(data, '$.Country') as Country,
FROM `****.***_raw_latest` 
WHERE Country = 'China'
LIMIT 50;

谢谢,

Answer 1

JSON_EXTRACT返回json格式的字符串，与BigQuery字符串不同。所以你不能像在查询中那样简单地比较两者。

CAST(JSON_EXTRACT(data, '$.Country') AS STRING) = 'China'

上面的表达式总是false，因为JSON_EXTRACT返回"China"，而不是China。

而JSON_VALUE直接从json字符串中提取BigQuery字符串并返回China，而不是"China"。

---

对于

，您可以考虑如下

我的目标是让代码在"data"通过对"数据"中包含的不同变量进行筛选，例如Country=China AND Online=true

WITH `foo.bar_raw_latest` AS (
SELECT "{"Code":"0000","Country":"China","Online":"true"}" data
)
SELECT JSON_VALUE(data, '$.Code') code
FROM `foo.bar_raw_latest`
WHERE JSON_VALUE(data, '$.Country') = 'China'
AND JSON_VALUE(data, '$.Online') = 'true';
-- Query results
+------+
| code |
+------+
| 0000 |
+------+