PostgreSQL中json数组值的交集

Question

在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. 映射第 n 行的 field_id 值...

...

last. 获取所有行的交集：[2]（假设表格只有两行）

有人能告诉我如何实现吗？

非常感谢

- milushov

---

请使用 JSONLint 来验证 JSON 值。 - klin

Answer 1

您需要使用聚合函数来交集连续行的数组：

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() 和 array_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)     

- klin

---

避免像“+1”或“谢谢”这样的评论，但是谢谢你，伙计，你太棒了！ - milushov