PostgreSQL查询jsonb列双引号报错的替代参数格式咨询

问题场景 #

现有PostgreSQL的orders表结构如下：

id | params   (jsonb type)              | valid
1  | {"value": "120", is_active: true}  | true
2  | {"value": "92", is_active: false}  | true

需要按params.is_active = true过滤数据，传入过滤方法的属性为：

filter.property = '(params ->>\'is_active\')::boolean'

但过滤方法会自动给属性值添加双引号，生成的SQL如下：

select * 
from "orders" 
where "valid" = true 
and "(params->>'is_active')::boolean" = true 
limit 50

执行时报错：

ERROR: column "(params->>'is_active')::boolean" does not exist.

移除双引号后语句可正常执行，但希望找到无需修改过滤方法或正则移除引号的直接替代方案。

解决方案 #

方案1：创建生成列（最优解） #

在orders表中添加一个基于params.is_active的生成列，将JSONB字段的布尔值提取为普通布尔列：

ALTER TABLE orders 
ADD COLUMN is_active boolean 
GENERATED ALWAYS AS ((params ->> 'is_active')::boolean) STORED;

之后只需传入普通列名作为过滤属性：

filter.property = 'is_active'
filter.value = true

生成的合法SQL如下：

select * 
from "orders" 
where "valid" = true 
and "is_active" = true 
limit 50

该方案不仅彻底避免了引号问题，还能通过给生成列创建索引提升查询性能。

方案2：使用JSONB包含操作符调整逻辑 #

如果无法修改表结构，可利用PostgreSQL的@>操作符检查JSONB字段是否包含指定键值对：
将filter.property设为params，filter.value设为'{"is_active": true}'，同时调整过滤方法生成"params" @> '{"is_active": true}'的条件（仅需修改条件的比较符，无需改动参数格式的处理逻辑）。

生成的有效SQL为：

select * 
from "orders" 
where "valid" = true 
and "params" @> '{"is_active": true}' 
limit 50

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