如何用SQL查询按key获取指定jsonb元素？

核心解决方案 #

针对需求——不管对象在数组中的位置，按指定key搜索匹配的jsonb对象，以下是几种实用方法：

1. 针对数组类型的jsonb列提取匹配对象 #

如果data列存储的都是数组，直接展开数组筛选：

SELECT elem
FROM data_set r,
     jsonb_array_elements(r.data) elem
WHERE elem @> '{"name": "Mouse"}'::jsonb;

• jsonb_array_elements会把数组拆分成单个元素，@>操作符判断元素是否包含目标键值对。
• 如果列里有非数组值（比如标量、null），先过滤数组类型的行避免报错：

SELECT elem
FROM data_set r,
     jsonb_array_elements(r.data) elem
WHERE jsonb_typeof(r.data) = 'array'
  AND elem @> '{"name": "Mouse"}'::jsonb;

2. 兼容多种jsonb类型的通用查询 #

如果data列混合了数组、单个对象、标量、null等多种类型，用jsonb_path_query统一处理，自动忽略不符合条件的内容：

SELECT jsonb_path_query(r.data, '$[*] ? (@.name == "Mouse")') AS matching_obj
FROM data_set r;

• $[*]遍历数组所有元素，即使列本身是单个对象也能匹配；
• ? (@.name == "Mouse")是筛选条件，匹配name值为'Mouse'的对象。

3. 动态指定搜索的key #

如果需要灵活切换搜索的key（比如不是固定的name），可以用参数化方式实现：

-- 示例：搜索key为"category"、值为"Electronics"的对象
SELECT jsonb_path_query(r.data, '$[*] ? (@."category" == "Electronics")') AS matching_obj
FROM data_set r;

在应用中使用参数化查询更安全（避免SQL注入）：

PREPARE search_jsonb(text, text) AS
SELECT jsonb_path_query(r.data, '$[*] ? (@."$1" == "$2")') AS matching_obj
FROM data_set r;

-- 执行时传入key和值
EXECUTE search_jsonb('name', 'Mouse');

4. 筛选存在匹配对象的行 #

如果只需要找出包含目标对象的行，不用提取具体对象，用jsonb_path_exists：

SELECT r.*
FROM data_set r
WHERE jsonb_path_exists(r.data, '$[*] ? (@.name == "Mouse")');

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