如何在Spanner数据库的JSONB列中查询数组内的特定字段

针对你需要从JSONB类型列的数组中提取所有"inter"字段值的需求，固定索引的方式无法覆盖数组中任意位置的元素，可通过数组展开+字段提取+过滤的方式实现：

基础查询语句 #

SELECT JSON_EXTRACT_SCALAR(item, '$.inter') AS inter_value
FROM your_table,
     UNNEST(JSON_EXTRACT_ARRAY(column_name, '$.data')) AS item
WHERE JSON_EXTRACT_SCALAR(item, '$.inter') IS NOT NULL

语句说明 #

• JSON_EXTRACT_ARRAY(column_name, '$.data')：从目标JSONB列中提取data对应的数组结构
• UNNEST(...) AS item：将数组展开为独立行，每行对应数组中的一个元素
• JSON_EXTRACT_SCALAR(item, '$.inter')：从每个数组元素中提取inter字段的字符串值
• WHERE子句：过滤掉不包含inter字段的元素，避免返回空值

关联原表字段的查询 #

如果需要保留原表的其他字段（比如主键id），可以调整语句如下：

SELECT t.id, JSON_EXTRACT_SCALAR(item, '$.inter') AS inter_value
FROM your_table t,
     UNNEST(JSON_EXTRACT_ARRAY(t.column_name, '$.data')) AS item
WHERE JSON_EXTRACT_SCALAR(item, '$.inter') IS NOT NULL

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