PostgreSQL中json数组的值的交集 [英] Intersection of values of json array in PostgreSQL

问题描述

在PostgreSQL 9.4中，我有一个像这样的表:

In PostgreSQL 9.4, I have a table like this:

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 值的交集.

I want to get the intersection of all field_id values in array_json column across all table.

| field_id intersection
+-------
| 2

我的意思是:

1..映射第一行的field_id值:[9，2]

1. map field_id values for first row: [9, 2]

2.映射第二行的field_id值:[10，2]

2. map field_id values for second row: [10, 2]

n.映射 n ...

...

最后.获取所有行的交集:[2](假设表只有两行)

last. get intersection for all rows: [2] (assume that table has only two rows)

有人可以告诉我该怎么做吗?

Can anybody tell me how this can be done?

非常感谢

推荐答案

您将需要一个聚合来与连续行中的数组相交:

You will need an aggregate to intersect arrays from consecutive rows:

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:

Use jsonb_array_elements() with array_agg() to retrieve field_ids in form of integer array:

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)    

使用已定义的相交集合与所有行相交的数组:

Use the defined intersection aggregate to intersect arrays accross all 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)     

这篇关于PostgreSQL中json数组的值的交集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！