milushov milushov - 4 months ago 37
JSON Question

Intersection of values of json array in PostgreSQL

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

Answer

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)