Postgres如何从包含多个JSON对象的数组中提取指定值

实现方案 #

你可以通过json_array_elements函数将dbSources数组展开为单个JSON对象的行，再通过条件过滤匹配srcIp后提取database字段即可，注意将语句中的your_table_name替换为实际的业务表名，最终查询语句如下：

SELECT (db_item ->> 'database') AS target_database
FROM your_table_name,
     json_array_elements(additional_info::json -> 'dbSources') AS db_item
WHERE db_item ->> 'srcIp' = '10.10.10.68';

如果你的additional_info列是jsonb类型，将json_array_elements替换为jsonb_array_elements即可，查询性能会更优。

语句逻辑说明

• json_array_elements(additional_info::json -> 'dbSources') AS db_item：把每行数据里的dbSources数组拆分为多条记录，每条记录对应数组中的一个JSON对象，别名设为db_item
• WHERE db_item ->> 'srcIp' = '10.10.10.68'：过滤出srcIp等于目标值的JSON对象，其中->>运算符会直接返回字符串类型的字段值
• (db_item ->> 'database') AS target_database：提取匹配对象的database字段值，返回字符串类型的结果

如果业务场景可能出现多个符合srcIp匹配条件的对象，上述语句会返回所有匹配的database值；若只需要取第一个匹配结果，可以在语句末尾追加LIMIT 1。

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