not understand json_agg in this context

Question

Reference: Query jsonb column containing array of JSON objects

begin;
CREATE TEMP TABLE segments (segments_id serial PRIMARY KEY, payload jsonb);
INSERT INTO segments (payload)
VALUES ('[{"kind": "person", "limit": "1"}, {"kind": "B", "filter_term": "fin"}]');
INSERT INTO segments (payload)
VALUES ('[{"kind": "person", "limit": "3"}, {"kind": "A", "filter_term": "abc"}]');
INSERT INTO segments (payload)
VALUES ('[{"kind": "person", "limit": "2"}, {"kind": "C", "filter_term": "def"}]');
commit;

CTE query:

 with a  as (select jsonb_array_elements(s.payload) j from segments s)
    SELECT  json_agg(a.j) AS filtered_payload from a
    where j @> '{"kind":"person"}';

Return: [{"kind": "person", "limit": "1"}, {"kind": "person", "limit": "3"}, {"kind": "person", "limit": "2"}]

This QueryA:

SELECT a.filtered_payload,
         a.ct_elem_row
        , 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) j(elem)
    WHERE  j.elem @> '{"kind":"person"}'
        ) a ON ct_elem_row > 0
WHERE  s.payload @> '[{"kind":"person"}]';

return :

In QueryA, the structure is like: select ... from segments s join lateral filtered_payload.... segments is 3 rows lateral join with one row (filtered_payload). filtered_payload return only row as per CTE query, as an a consolidate JSON array. So overall I am very confused with json_agg in the QueryA.

Edit at 2021-10-05 16:36 +5:30: Even following code, a.filtered_payload return 3 jsonb array, instead of one arrgregate json array. I don't know when already aggregated jsonb array (using json_agg function) unnested to serveal jsonb arrays.

 SELECT a.filtered_payload, s.*
FROM   segments s
           cross  JOIN   LATERAL (
    SELECT json_agg(j.elem) AS filtered_payload
    FROM   jsonb_array_elements(s.payload) j(elem)
    WHERE  j.elem @> '{"kind":"person"}') a;

Of course that query returns three results; after all, you `SELECT /* no aggregate function */ FROM segments ...`. That must give you at least one result row per row in `segments`. What are you trying to achieve? — Laurenz Albe, Oct 05 '21 at 12:50
@LaurenzAlbe I thought the last query justlooks like `SELECT a.* FROM segments s cross JOIN LATERAL ('[{"kind": "person", "limit": "1"}, {"kind": "person", "limit": "3"}, {"kind": "person", "limit": "2"}]' ::jsonb) a;` But this query have syntax error. I still not getting it. we already aggregated the jsonb to an single array. '[{"kind": "person", "limit": "1"}, {"kind": "person", "limit": "3"}, {"kind": "person", "limit": "2"}]' . How can filtered_payload only return one object. — jian, Oct 06 '21 at 06:37

score 0 · Accepted Answer · answered Oct 05 '21 at 10:31

I believe the LATERAL JOIN is doing the trick there. In your original query, you're using json_agg against the whole table dataset (filtered by '{"kind":"person"}')

with a  as 
    (
        select jsonb_array_elements(s.payload) j 
        from segments s
    )
SELECT  json_agg(a.j) AS filtered_payload 
from a
where j @> '{"kind":"person"}';

Meanwhile in the second instance, you are playing with one row at the time using the LATERAL. That's why you end up having 3 rows with single "kind":"person" values instead of an unique row with 3 values.

Not sure about what you're trying to achieve but the following could put you in the right direction

SELECT a.filtered_payload,
    a.ct_elem_row, 
    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   segments d, lateral jsonb_array_elements(d.payload) j(elem)
    WHERE  j.elem @> '{"kind":"person"}'
    ) a ON ct_elem_row > 0
WHERE  s.payload @> '[{"kind":"person"}]';

Results

                                            filtered_payload                                            | ct_elem_row | ct_elem_total | ct_rows
--------------------------------------------------------------------------------------------------------+-------------+---------------+---------
 [{"kind": "person", "limit": "1"}, {"kind": "person", "limit": "3"}, {"kind": "person", "limit": "2"}] |           3 |             9 |       3
 [{"kind": "person", "limit": "1"}, {"kind": "person", "limit": "3"}, {"kind": "person", "limit": "2"}] |           3 |             9 |       3
 [{"kind": "person", "limit": "1"}, {"kind": "person", "limit": "3"}, {"kind": "person", "limit": "2"}] |           3 |             9 |       3
(3 rows)

not understand json_agg in this context

1 Answers1

Linked