json取交集,PostgreSQL中json数组的值的交集

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}, ...]

...

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

| field_id intersection

+-------

| 2

I mean:

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

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

n. map field_id values for n ...

...

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

Can anybody tell me how this can be done?

Many thanks in advance

解决方案

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

);

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)