How to apply a filter on jsonb array of objects - after aggregating?

Question

I have a the follow select statement:

SELECT 
    cards.*, 
    COUNT(cards.*) OVER() AS full_count, 
    p.printing_information 
FROM 
    cards
LEFT JOIN 
    (SELECT 
         pr.card_id, jsonb_agg(to_jsonb(pr)) AS printing_information
     FROM 
         printings pr
     GROUP BY 
         pr.card_id) p ON cards.card_id = p.card_id 
WHERE 
    ...

I would like to be able to query on set_id that is within the printings table. I tried to do this within my above select statement by including pr.set_id but it then required a GROUP BY pr.card_id, pr.set_id which then made a row per printing rather than having all printings within the printing_information sub-array.

Unless I can determine how to do above, is it possible to search within the printing_information array of jsonb?

Ideally I would like to be able to do something like:

WHERE p.printing_information->set_id = '123'

Unfortunately I can't do that as it's within an array.

What's the best way to achieve this? I could just do post-processing of the result to strip out unnecessary results, but I feel there must be a better way.

Answer 1

SELECT cards.*
     , count(cards.*) over() AS full_count
     , p.printing_information
FROM   cards
LEFT   JOIN (
   SELECT pr.card_id, jsonb_agg(to_jsonb(pr)) AS printing_information
   FROM   printings pr
   WHERE  pr.set_id = '123'            -- HERE!
   GROUP  BY pr.card_id
   ) p ON cards.card_id = p.card_id
WHERE  ...

This is much cheaper than filtering after the fact. And can be supported with an index on (set_id) - unlike any attempts to filter on the dynamically generated jsonb column.

This is efficient, while we need to aggregate all or most rows from table printings anyway. But your added WHERE ... implies more filters on the outer SELECT. If that results in only few rows from printings being needed, a LATERAL subquery should be more efficient:

SELECT cards.*
     , count(cards.*) OVER() AS full_count
     , p.printing_information
FROM   cards c
CROSS  JOIN LATERAL (
   SELECT jsonb_agg(to_jsonb(pr)) AS printing_information
   FROM   printings pr
   WHERE  pr.card_id = c.card_id
   AND    pr.set_id = '123'    -- here!
   ) p
WHERE  ...  -- selective filter here?!?

See:

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

Aside, there is no "array of jsonb" here. The subquery produces a jsonb containing an array.