pOSTGRESQL JSONB FROM: http://stackoverflow.com/questions/38679190/query-jsonb-column-containing-array-of-json-objects

Assuming this table definition:

CREATE TABLE segments (segments_id serial PRIMARY KEY, payload jsonb);
With JSON values like this:

INSERT INTO segments (payload)
VALUES ('{
     "a": [
        {
            "kind": "person",
            "limit": "1",
            "filter_term": "56",
            "selected_attr": "customer",
            "selected_operator": "less_than"
        },
        {
            "kind": "email",
            "filter_term": "marketer",
            "selected_attr": "job_title",
            "selected_operator": "equals"
        }
      ]
   }'
 );

You want to return elements of the JSON array with the key "a", that contain a key/value pair "kind":"person" (not a nested JSON object {"kind":"person"}) - and count array elements as well as table rows (there may be multiple matching array elements per row)

Solutions

To get the count of rows containing a qualifying jsonb value in column segments:

SELECT count(*)
FROM   segments s
WHERE  s.payload->'a' @> '[{"kind":"person"}]';

To get all qualifying JSON array elements (being JSON objects themselves) - plus the total count of elements (may be greater than above count at the same time:

SELECT j.*
FROM   segments s
JOIN   LATERAL jsonb_array_elements(s.payload->'a') j(elem) ON j.elem @> '{"kind":"person"}'
WHERE  s.payload->'a' @> '[{"kind":"person"}]';

Returns:

elem
------------------------------------------------------------
{"kind": "person", "limit": "1", "filter_term": "56", ... }

To get all at once:

SELECT j.*, count(*) OVER () AS ct_elem, s.ct_rows
FROM  (
   SELECT payload, count(*) OVER () AS ct_rows
   FROM   segments s
  WHERE  s.payload->'a' @> '[{"kind":"person"}]'
 ) s
 JOIN   LATERAL jsonb_array_elements(s.payload->'a') j(elem) ON j.elem @> '{"kind":"person"}';

Returns (for a table with more entries):

elem                      | ct_elem | ct_rows
--------------------------+---------+---------
{"kind": "person",  ... } | 4       | 3
 {"kind": "person",  ... } | 4       | 3
...

But I think you really want this:

SELECT a.*
 , sum(ct_elem_row) OVER () AS ct_elem_total
 , count(*)         OVER () AS ct_rows
FROM   segments s
JOIN   LATERAL (
   SELECT json_agg(j.elem) AS filtered_payload, count(*) AS ct_elem_row
   FROM   jsonb_array_elements(s.payload->'a') j(elem)
   WHERE  j.elem @> '{"kind":"person"}'
   ) a ON ct_elem_row > 0
WHERE  s.payload->'a' @> '[{"kind":"person"}]';

Returns (for a table with more entries):

filtered_payload                                     | ct_elem_row | ct_elem_total | ct_rows
-----------------------------------------------------+-------------+---------------+---------
[{"kind": "person", ... }]                           | 1           | 4             | 3
[{"kind": "person", ... }]                           | 1           | 4             | 3
[{"kind": "person", ... }, {"kind": "person", ... }] | 2           | 4             | 3

This identifies matching rows, then select matching array elements and builds an array per row with only those. Plus counts.

For best performance you would have a functional jsonb_path_ops GIN index like:

CREATE INDEX segments_a_path_ops_gin_idx ON segments 
USING  gin ((payload->'a') jsonb_path_ops);

(But a more generic index to serve more different queries may be a better choice.)

Terminology

We are dealing with a JSON object containing a JSON array, saved as Postgres jsonb data type - a "JSON array" for short, but not an "array of JSON".

amitmbee/jsonb.MD