PostgreSQL中row_to_json生成的JSON字段按key过滤失败求助

能从JSON字段中提取worker_id的值，但用该值作为过滤条件时触发报错，错误提示如下：

提示：没有匹配给定名称和参数类型的运算符。您可能需要添加显式类型转换。

相关SQL代码如下：

1. 创建视图的SQL #

drop if exists worker_responses_view
create or replace view worker_responses_view as
    select  
           row_to_json(hrm_orderresponse.*) as hrm_orderresponse_json,
           row_to_json(hrm_worker.*) as hrm_worker_json,
           row_to_json(hrm_orderperdayitem.*) as hrm_orderperdayitem_json,
           row_to_json(hrm_order.*) as hrm_order_json,
           row_to_json(hrm_branch.*) as hrm_branch_json
    from hrm_orderresponse, hrm_worker, hrm_orderperdayitem, hrm_order, hrm_company, hrm_branch
    where hrm_orderresponse.worker_id = hrm_worker.id
        and hrm_orderresponse.order_item_id = hrm_orderperdayitem.id
        and hrm_orderperdayitem.order_id = hrm_order.id
        and hrm_order.company_id = hrm_company.id
        and hrm_order.company_branch_id = hrm_branch.id;

2. 可正常执行的JSON字段提取查询 #

select hrm_orderresponse_json, hrm_orderresponse_json->>'worker_id' as worker_id
from worker_responses_view
limit 1;

3. 执行报错的过滤查询 #

select hrm_orderresponse_json, hrm_orderresponse_json->>'worker_id' as worker_id
from worker_responses_view
where hrm_orderresponse_json->>'worker_id' = 1004;

原因分析 #

->>操作符提取的是文本类型的JSON值，而WHERE条件中直接和数值1004做相等判断时，PostgreSQL无法自动匹配文本与数值类型的比较运算符，因此触发类型不匹配错误。

解决方法 #

有两种可行的修正方式：

方式1：将数值转为字符串比较 #

把1004用单引号包裹，作为字符串与提取出的文本值比较：

select hrm_orderresponse_json, hrm_orderresponse_json->>'worker_id' as worker_id
from worker_responses_view
where hrm_orderresponse_json->>'worker_id' = '1004';

方式2：将提取的JSON值转为数值类型 #

使用::integer显式将提取的文本转为整数，再与数值比较：

select hrm_orderresponse_json, hrm_orderresponse_json->>'worker_id' as worker_id
from worker_responses_view
where (hrm_orderresponse_json->>'worker_id')::integer = 1004;

也可以先用->操作符获取JSON类型的值，再转为整数：

select hrm_orderresponse_json, hrm_orderresponse_json->>'worker_id' as worker_id
from worker_responses_view
where (hrm_orderresponse_json->'worker_id')::integer = 1004;

额外优化建议 #

无需先将整表转成JSON再过滤，直接基于原表字段查询效率更高：

select  
       row_to_json(hrm_orderresponse.*) as hrm_orderresponse_json,
       row_to_json(hrm_worker.*) as hrm_worker_json,
       row_to_json(hrm_orderperdayitem.*) as hrm_orderperdayitem_json,
       row_to_json(hrm_order.*) as hrm_order_json,
       row_to_json(hrm_branch.*) as hrm_branch_json
from hrm_orderresponse, hrm_worker, hrm_orderperdayitem, hrm_order, hrm_company, hrm_branch
where hrm_orderresponse.worker_id = hrm_worker.id
    and hrm_orderresponse.order_item_id = hrm_orderperdayitem.id
    and hrm_orderperdayitem.order_id = hrm_order.id
    and hrm_order.company_id = hrm_company.id
    and hrm_order.company_branch_id = hrm_branch.id
    and hrm_orderresponse.worker_id = 1004; -- 直接用原始字段过滤

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