如何在BigQuery SQL中提取嵌套JSON的account->accounts数据

问题场景 #

我有一份嵌套JSON数据存储在BigQuery表中，需要提取account节点下accounts对应的两个值（1和2），但自己编写的查询执行后返回NULL，求正确的查询写法。

示例JSON：

{
  "added": [],
  "data_import": null,
  "removed": [],
  "row_changed": [
    [
      "account",
      0,
      "6b72e3117c",
      [
        [
          "accounts",
          "1",
          "2"
        ],
        [
          "expense_account",
          "TEST",
          "TEST2"
        ]
      ]
    ],
    [
      "payment_schedule",
      0,
      "7ba5ca5175",
      [
        [
          "dt",
          "31-03-2023",
          "31-07-2023"
        ],
        [
          "test",
          "$ 0",
          "$ 1"
        ]
      ]
    ]
  ],
  "updater_reference": null
}

原查询语句：

SELECT DISTINCT col1 ,col2, col3, 
JSON_VALUE(row_changed, '$.0.3.0') AS col,
FROM bigquery_table,
UNNEST(JSON_EXTRACT_ARRAY(col3, '$.row_changed')) row_changed

问题分析 #

原查询存在两个核心问题：

1. UNNEST后的row_changed是数组中的单个元素（比如第一个是["account",0,"6b72e3117c",[...]]），用$.0.3.0的路径完全错误——这个元素本身是数组，不是带键的JSON对象，不能用对象键的方式访问。
2. 就算路径正确，JSON_VALUE只能提取单个值，无法同时拿到accounts对应的1和2，需要进一步拆解嵌套数组。

修正后的查询语句 #

SELECT 
  col1, col2, col3,
  arr[OFFSET(1)] AS account_old,
  arr[OFFSET(2)] AS account_new
FROM bigquery_table,
  -- 拆解最外层的row_changed数组
  UNNEST(JSON_EXTRACT_ARRAY(col3, '$.row_changed')) AS rc,
  -- 拆解account节点里的子属性数组（rc的第4个元素，索引为3）
  UNNEST(JSON_EXTRACT_ARRAY(rc, '$[3]')) AS arr
WHERE 
  -- 筛选出account类型的节点
  JSON_VALUE(rc, '$[0]') = 'account'
  -- 筛选出accounts对应的子数组
  AND JSON_VALUE(arr, '$[0]') = 'accounts'

关键步骤说明 #

• 先通过UNNEST(JSON_EXTRACT_ARRAY(col3, '$.row_changed'))拆解最外层的row_changed数组，得到每个独立节点（比如account和payment_schedule）。
• 用JSON_VALUE(rc, '$[0]') = 'account'过滤出我们需要的account节点。
• 再拆解该节点中索引为3的子数组（也就是包含accounts和expense_account的数组）。
• 最后筛选出accounts对应的子数组，用OFFSET(1)和OFFSET(2)提取对应的两个目标值。

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