如何在BigQuery中查询含@符号的JSON键@type的值？

问题场景 #

• 在日志探索器（Log Explorer）中，可通过以下语法过滤日志：

  protoPayload.metadata."@type"="type.googleapis.com/google.cloud.audit.BigQueryAuditMetadata"
  

• 将日志通过接收器导入BigQuery后，尝试用以下SQL查询@type值时触发错误：

  JSON_EXTRACT(PARSE_JSON(protopayload_auditlog.metadataJson), '$.@type')
  

  错误信息：Unsupported operator in JSONPath: @;
• 示例JSON payload：

  {
    "@type": "type.googleapis.com/google.cloud.audit.BigQueryAuditMetadata",
    "tableDataRead": {
      "fields": [
        "insertId",
        "logName",
        "protopayload_auditlog.metadataJson",
        "protopayload_auditlog.methodName",
        "receiveTimestamp",
        "resource.labels.dataset_id",
        "resource.labels.location",
        "resource.labels.project_id",
        "resource.type",
        "severity"
      ],
      "jobName": "projects/element-analytics-dev-poc/jobs/job_XvacfHSAAr_Og7zQFVNV9ioV9vfu",
      "reason": "JOB"
    }
  }
  

解决方法 #

由于@在BigQuery的JSONPath中有特殊含义，不能直接用$.@type访问，可通过以下几种方式正确查询：

方法1：使用JSONPath的方括号引号语法

JSON_EXTRACT(PARSE_JSON(protopayload_auditlog.metadataJson), '$["@type"]')

方法2：使用JSON_VALUE简化查询（直接返回字符串）

JSON_VALUE(protopayload_auditlog.metadataJson, '$["@type"]')

方法3：解析JSON后用反引号包裹字段名访问

PARSE_JSON(protopayload_auditlog.metadataJson).`@type`

内容的提问来源于stack exchange，提问作者Moseleyi