在PostgreSQL 9.4中，我有一个像这样的表：
id | array_json
---+----------------------------
1 | [{"type": "single", "field_id": 9},
| {"type": "range", "field_id": 2}, ...]
|
2 | [{"type": "single", "field_id": 10},
| {"type": "range", "field_id": 2}, ...]
...
我想在所有表的array_json列中获取所有field_id值的交集。
| field_id intersection
+-------
| 2
我的意思是：
1.映射第一行的field_id值：[9，2]
2.映射第二行的field_id值：[10，2]
。映射n的field_id值...
...
持续。获取所有行的交集：[2]（假设表只有两行）
谁能告诉我该怎么做？
提前谢谢了
您将需要一个聚合来与连续行中的数组相交：
create or replace function array_intersect(anyarray, anyarray)
returns anyarray language sql
as $$
select
case
when $1 is null then $2
when $2 is null then $1
else
array(
select unnest($1)
intersect
select unnest($2))
end;
$$;
create aggregate array_intersect_agg (anyarray)
(
sfunc = array_intersect,
stype = anyarray
);
jsonb_array_elements()与witharray_agg()一起使用以field_ids整数数组形式进行检索：
select id, array_agg(field_id) field_ids
from (
select id, (e->>'field_id')::int field_id
from a_table, jsonb_array_elements(array_json) e
) sub
group by 1
order by 1;
id | field_ids
----+-----------
1 | {9,2}
2 | {10,2}
(2 rows)
使用定义的交集聚合可将所有行上的数组相交：
select array_intersect_agg(field_ids)
from (
select id, array_agg(field_id) field_ids
from (
select id, (e->>'field_id')::int field_id
from a_table, jsonb_array_elements(array_json) e
) sub
group by 1
order by 1
) sub;
array_intersect_agg
---------------------
{2}
(1 row)
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句