我需要从表中选择与涉及jsonb对象字段比较的特定条件匹配的行。
在下面的示例中,我只想获取值在数组中的对象指定的最小/最大范围内的行:对于任何给定行,如果其array_of_objects“中的任何对象包含(使用最小/最大比较)avalue,则需要该行。

CREATE TABLE test_table (
  id serial,
  value int,
  array_of_objects jsonb[]
);

INSERT INTO
  test_table (value, array_of_objects)
VALUES
  (8, ARRAY ['{"min":5,"max":15}', '{"min":4,"max":18}']::jsonb[]),
  (6, ARRAY ['{"min":12,"max":18}', '{"min":19,"max":22}']::jsonb[]),
  (22, ARRAY ['{"min":16,"max":18}', '{"min":34,"max":47}']::jsonb[]);

所以对于给定的例子,我只得到值822的行。

最佳答案

如果需要原始列:

t=# with a as (select unnest(array_of_objects) j,* from test_table)
select distinct id,value, array_of_objects
from a
where (j->>'min')::int < value and (j->>'max')::int > value;
 id | value |                     array_of_objects
----+-------+-----------------------------------------------------------
  1 |     8 | {"{\"max\": 15, \"min\": 5}","{\"max\": 18, \"min\": 4}"}
(1 row)

这里是“解释”为什么值22没有进入其中(数组对象[1]>>max小于22:
Time: 0.441 ms
t=# with a as (select unnest(array_of_objects) j,* from test_table)
select distinct id,value,j
from a
where (j->>'min')::int < value and (j->>'max')::int > value;
 id | value |           j
----+-------+-----------------------
  1 |     8 | {"max": 18, "min": 4}
  1 |     8 | {"max": 15, "min": 5}
(2 rows)

Time: 0.390 ms

07-24 09:50
查看更多