如何在未知字段路径/别名时，限定PostgreSQL查询作用于指定JSONB字段？

我有一张包含多个结构相似JSONB字段的表：

create table dog (id text, leftear jsonb, rightear jsonb);

insert into dog (id, leftear, rightear) values
  ('a', '{"itchy": false}', '{"itchy": false}'),
  ('b', '{"itchy": true}',  '{"itchy": false}'),
  ('c', '{"itchy": false}', '{"itchy": true}'),
  ('d', '{"itchy": true}',  '{"itchy": true}');

我的查询生成器仅输出两种查询片段，完全不知道要关联哪个耳朵字段，我希望维持这个无感知状态：

• 原始条件片段：itchy == true
• 带类型转换的片段：itchy::boolean IS TRUE

我需要设计SQL语句，让这些片段能直接应用到任意一个耳朵字段上。目前尝试了几种方案，但都存在局限：

现有尝试的局限性 #

• 直接字段访问：必须明确指定目标JSONB字段，违背查询生成器无感知的要求

  select id from dog where (leftear->'itchy')::boolean IS TRUE;
  

• jsonb_to_record：需要声明JSON结构且指定别名，同样需要知晓目标字段

  select id from dog, jsonb_to_record(dog.leftear) as le(itchy boolean) where le.itchy IS TRUE;
  

• JSONB包含操作：功能受限，仅能匹配精确键值对，无法处理复杂条件（如范围判断）

  select id from dog where leftear @> '{"itchy": true}'::jsonb;
  

• jsonb_path_exists函数：语法冗余，需要手动拼接JSON路径

  select id from dog where jsonb_path_exists(leftear, '$.itchy ? (@ == true)');
  

• JSON路径操作符@?：相对最优，但需要将条件片段转换为JSON路径语法

  select id from dog where leftear @? '$ ? (@.itchy == true)'::jsonpath;
  

理想状态下，我希望能用类似以下的简洁语法：

-- 该语法无效
select id from dog where leftear matches (itchy == true)

方案1：基于JSON路径操作符@?的拼接策略 #

利用PostgreSQL的@?JSON路径操作符，将查询生成器输出的片段包裹为标准JSON路径模板即可。

• 对于原始片段itchy == true，拼接后SQL为：

  select id from dog where leftear @? '$ ? (@.itchy == true)'::jsonpath;
  

• 对于带类型转换的片段itchy::boolean IS TRUE，可以转换为JSON路径的类型断言语法：

  select id from dog where leftear @? '$ ? (@.itchy is boolean && @.itchy == true)'::jsonpath;
  

优势

1. 查询生成器无感知：仅需输出核心条件，目标JSONB字段可在外部灵活拼接
2. 功能完整：支持多条件组合、范围判断等复杂逻辑，比包含操作更灵活
3. 语法紧凑：相比其他函数式写法，@?操作符的可读性更高

方案2：自定义函数实现类matches语法 #

如果想更接近理想的matches语法，可以创建一个自定义SQL函数来封装逻辑：

create or replace function jsonb_matches(jsonb_field jsonb, condition text) returns boolean as $$
begin
  return jsonb_field @? ('$ ? (@.' || condition || ')')::jsonpath;
end;
$$ language plpgsql immutable;

之后就能用接近理想的语法查询：

select id from dog where jsonb_matches(leftear, 'itchy == true');
select id from dog where jsonb_matches(rightear, 'itchy == true');

注意：自定义函数需要处理输入条件的合法性，建议在查询生成器中对条件做转义或语法校验，避免SQL注入风险。

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