如何使用BigQuery SQL在现有JSON列中添加新键值对

针对BigQuery中的JSON列（包括原生JSON类型和字符串存储的JSON），可以用以下方法添加新的键值对：

1. 原生JSON类型列（BigQuery 2023+支持） #

直接使用JSON_SET函数修改JSON对象，这个函数支持添加新键值对，若键已存在则会覆盖原有值。

基础示例 #

假设你的表有一个名为json_data的JSON列，要添加"status": "active"键值对：

UPDATE `your-project.your-dataset.target-table`
SET json_data = JSON_SET(json_data, '$.status', 'active')
-- 可选：添加WHERE子句过滤需要更新的行
WHERE id = 123;

嵌套键值对示例 #

如果要在嵌套结构中添加新键，比如给$.user.profile添加"phone": "123456789"：

UPDATE `your-project.your-dataset.target-table`
SET json_data = JSON_SET(json_data, '$.user.profile.phone', '123456789')
WHERE id = 123;

2. 字符串类型存储的JSON #

如果你的JSON数据是以字符串形式存在（比如STRING类型列），需要先解析为JSON对象，修改后再转回字符串：

UPDATE `your-project.your-dataset.target-table`
SET json_string_col = TO_JSON_STRING(
  JSON_SET(JSON_PARSE(json_string_col), '$.new_key', 'new_value')
)
WHERE id = 123;

注意事项 #

• 若要避免覆盖已存在的键，可以先用JSON_CONTAINS_PATH检查键是否存在，再决定是否执行更新：

  UPDATE `your-project.your-dataset.target-table`
  SET json_data = JSON_SET(json_data, '$.status', 'active')
  WHERE NOT JSON_CONTAINS_PATH(json_data, 'one', '$.status');
  

• 数组元素的路径写法：比如给数组$.items的第一个元素添加键，用$.items[0].new_key
• 批量更新不同键值对时，可以结合CASE语句实现自定义逻辑

效果示例 #

修改前JSON：

{"name": "Alice", "age": 30}

执行添加"status": "active"的SQL后，JSON变为：

{"name": "Alice", "age": 30, "status": "active"}

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