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