PostgreSQL匹配结束时间删除JSONB值报错的技术咨询

我有两张表Product_details和Pricing，需求是：

• 当start_date匹配当前日期时间时，更新Product_details表，插入对应id的优惠/促销价格
• 当end_date匹配当前日期时间时，更新Product_details表，删除对应id的优惠/促销价格

可正常运行的插入代码 #

UPDATE product_Details 
SET price_and_price_Type = price_and_price_Type || (pps.details::jsonb)
FROM ( 
    select id, price_Details as details 
    from pricing 
    where to_char(start_date,'YYYY-MM-DD HH24:MI') = to_char(now(),'YYYY-MM-DD HH24:MI')
    group by id, details
) as pps 
where product_Details.id = "pps".id ;

无法运行的删除代码及报错 #

删除代码：

UPDATE product_Details 
SET price_and_price_Type = price_and_price_Type - (pps.details::jsonb)
FROM ( 
    select id, price_Details as details 
    from pricing 
    where to_char(end_date,'YYYY-MM-DD HH24:MI') = to_char(now(),'YYYY-MM-DD HH24:MI')
    group by id, details
) as pps 
where product_Details.id = "pps".id ;

报错信息：

ERROR:  operator does not exist: jsonb - jsonb
LINE 2:    SET price_and_price_Type = price_and_price_Type -
                                                           ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

解决方法 #

PostgreSQL的jsonb类型中，-操作符仅支持两种场景：删除指定键（参数为text类型）、删除数组指定索引（参数为int类型），不能直接用两个jsonb对象相减来删除匹配的元素。需根据price_and_price_Type的实际结构调整代码：

场景1：price_and_price_Type是JSON数组

如果字段是数组格式（例如[{"type":"promo", "price":100}, {"type":"original", "price":200}]），可以展开数组过滤掉匹配元素后重新聚合：

UPDATE product_Details pd
SET price_and_price_Type = COALESCE(
    (SELECT jsonb_agg(elem)
     FROM jsonb_array_elements(pd.price_and_price_Type) elem
     WHERE elem != pps.details::jsonb),
    '[]'::jsonb
)
FROM ( 
    select id, price_Details as details 
    from pricing 
    where to_char(end_date,'YYYY-MM-DD HH24:MI') = to_char(now(),'YYYY-MM-DD HH24:MI')
    group by id, details
) as pps 
where pd.id = pps.id ;

场景2：price_and_price_Type是JSON对象

如果字段是键值对格式（例如{"promo_price":100, "original_price":200}），且pps.details是单个键值对（例如{"promo_price":100}），可提取键名后用-操作符删除：

UPDATE product_Details pd
SET price_and_price_Type = pd.price_and_price_Type - (jsonb_object_keys(pps.details::jsonb))
FROM ( 
    select id, price_Details as details 
    from pricing 
    where to_char(end_date,'YYYY-MM-DD HH24:MI') = to_char(now(),'YYYY-MM-DD HH24:MI')
    group by id, details
) as pps 
where pd.id = pps.id ;

注意事项 #

• jsonb的匹配是严格的，包括键的顺序和值的类型，需确保pps.details与要删除的元素完全一致
• 使用COALESCE避免数组过滤后为空时字段变成null

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