PostgreSQL中基于价格匹配JSONB数组对象获取对应count值

解决方案SQL #

假设你的表名为products，且f_struc字段类型为jsonb（若为json类型，只需将以下SQL中的jsonb_*函数替换为json_*即可）：

SELECT 
    p.product_id,
    p.price,
    COALESCE(MAX((kv.value->>'count')::int), 'n/a') AS matched_count
FROM 
    products p
LEFT JOIN LATERAL jsonb_array_elements(p.f_struc) arr(el) ON true
LEFT JOIN LATERAL jsonb_each(arr.el) kv(key, value) ON true
WHERE 
    p.price >= (kv.value->>'low')::numeric 
    AND p.price < (kv.value->>'high')::numeric
GROUP BY 
    p.product_id, p.price
ORDER BY 
    p.product_id;

逻辑解释 #

1. 展开JSONB数组：使用jsonb_array_elements将f_struc中的数组元素逐个拆分，每个元素是包含多个区间键值对的对象。
2. 拆分区间键值对：通过jsonb_each将每个数组元素中的键（如s1、s2）和对应的区间对象（包含low、high、count）拆分开。
3. 匹配价格区间：将区间的low和high转换为数值类型，与price做左闭右开的区间匹配（符合你的示例：15.6属于15-20区间）。
4. 聚合结果：用COALESCE和MAX确保每个商品只返回一个匹配的count值，若无匹配则返回n/a。

测试示例数据 #

先插入你的示例数据：

CREATE TABLE products (
    product_id int,
    price numeric,
    f_struc jsonb
);

INSERT INTO products VALUES
(1, 13.4, '[{"s1": {"low": 0, "high": 15, "count": 5}, "s2": {"low": 15, "high": 20, "count": 10}}]'),
(2, 15.6, '[{"s1": {"low": 0, "high": 15, "count": 5}, "s2": {"low": 15, "high": 20, "count": 10}}]'),
(3, 24.5, '[{"s1": {"low": 0, "high": 15, "count": 5}, "s2": {"low": 15, "high": 20, "count": 10}, "s1": {"low": 20, "high": 25, "count": 7}}]');

运行解决方案SQL后，返回结果：

注意事项 #

• 若你的f_struc是json类型，只需将jsonb_array_elements替换为json_array_elements，jsonb_each替换为json_each。
• 区间匹配规则可根据需求调整：比如若需要包含high值，可将条件改为p.price <= (kv.value->>'high')::numeric。
• 若存在多个匹配区间（同一商品的price落在多个区间），MAX会返回最大的count值，若需返回第一个匹配的，可改用FIRST_VALUE结合窗口函数。

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