postgresql - 在 PostgreSQL 中，如何选择具有范围内任何元素的 jsonb 数组的行？

Question

我有以下(简化的)结构:

 id |            j            
----+-------------------------
  1 | {"price": [1, 2, 3, 4]}
  2 | {"price": [4, 5, 6, 7]}
  3 | {"something": "else"}

理想情况下，我想查询这样的内容:

select id from testjson where any(j->'price') between 5 and 8;

我想出了一个解决方法:

select id from testjson where any_between(j->'price',5,8);

之前定义了以下内容:

CREATE OR REPLACE FUNCTION jsonb_array_bigint(_j jsonb) RETURNS bigint[] AS
$$
SELECT array_agg(elem::text::bigint) FROM jsonb_array_elements(_j) AS elem
$$
LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION any_between(_j jsonb,lower bigint, higher bigint) RETURNS boolean AS
$$
select (lower <= any(arr) and higher >= any(arr)) from jsonb_array_bigint(_j) as arr  
$$
LANGUAGE sql IMMUTABLE;

这行得通，但有点老套，而且看起来效率不高。有没有更好的方法？

Answer 1

您可以使用 json_array_elements 将数组扩展为行:

select  distinct id
from    Table1
cross join    
        json_array_elements((j->'price')::json)
where   value::text::int between 5 and 8

Example at SQL Fiddle.该示例适用于 json 而不是 jsonb 因为 SQL Fiddle 尚不支持 9.4。