查询BigQuery中的JSON值

Question

我有一个BigQuery表（“活动”），其中一个列（“组”）包含一个JSON值（该表是Google Workspace日志的导出）。

我希望能够根据 group> group 列中的 group_email JSON值从表中选择行。我检查了docs

SELECT
  record_type,
  email,
  JSON_VALUE('groups.group_email') AS group_email

但是无效。我还尝试使用 group_email 的列索引，

SELECT
  record_type,
  email,
  JSON_VALUE('groups[4]') AS group_email

但没有运气。我尝试了这些相同的组合作为Where语句的一部分，但得到了相同的结果：

SELECT
  *
FROM
  `company.workspace_prod.activity`
WHERE
  record_type = 'groups'
  and
  JSON_VALUE('groups.group_email') = '[email protected]'
LIMIT
  10

我还看到此答案但是使用 JSON_EXTRACT_SCALAR（'组'，“ $ .groups [0] .group_email'）作为group_email 也返回

任何我的想法我做错了吗？

Answer 1

您可以尝试以下方法。

SELECT
  record_type,
  email,
  JSON_VALUE(groups,'$.group_email') AS group_email
FROM
  `company.workspace_prod.activity`

您可以参考此 json函数文档以获取更多详细信息。

请使用JSON_VALUE的正确语法请参阅下面我的测试的屏幕截图。

示例数据：

int64_field_0	string_field_1
1	20
2	“这是字符串”
3	{“ id”：10，“ name”：“ Alice”}

查询结果：

Answer 2

虽然我迟到了，但我能够通过

SELECT
  record_type,
  email,
  json_extract(groups,'$.group_email') AS group_email
FROM
  `company.workspace_prod.activity`