Tips on how to Filter a SQL Nested Assortment by a Worth – Java, SQL and jOOQ.

I stumbled upon a really fascinating query on Stack Overflow about learn how to use jOOQ’s MULTISET operator to nest a set, after which filter the outcome by whether or not that nested assortment accommodates a worth.

The query is jOOQ particular, however think about, you’ve got a question that nests collections utilizing JSON in PostgreSQL. Assuming, as at all times, the Sakila database. Now, PostgreSQL doesn’t help the SQL commonplace MULTISET operator, however we are able to use ARRAY, which works virtually the identical method.

SELECT
  f.title,
  ARRAY(
    SELECT ROW(
      a.actor_id,
      a.first_name,
      a.last_name
    )
    FROM actor AS a
    JOIN film_actor AS fa USING (actor_id)
    WHERE fa.film_id = f.film_id
    ORDER BY a.actor_id
  )
FROM movie AS f
ORDER BY f.title

This produces all movies and their actors as follows (I’ve truncated the arrays for readability functions. You get the purpose):

title                      |array                                                                                 
---------------------------+--------------------------------------------------------------------------------------
ACADEMY DINOSAUR           |{"(1,PENELOPE,GUINESS)","(10,CHRISTIAN,GABLE)","(20,LUCILLE,TRACY)","(30,SANDRA,PECK)"
ACE GOLDFINGER             |{"(19,BOB,FAWCETT)","(85,MINNIE,ZELLWEGER)","(90,SEAN,GUINESS)","(160,CHRIS,DEPP)"}   
ADAPTATION HOLES           |{"(2,NICK,WAHLBERG)","(19,BOB,FAWCETT)","(24,CAMERON,STREEP)","(64,RAY,JOHANSSON)","(1
AFFAIR PREJUDICE           |{"(41,JODIE,DEGENERES)","(81,SCARLETT,DAMON)","(88,KENNETH,PESCI)","(147,FAY,WINSLET)"
AFRICAN EGG                |{"(51,GARY,PHOENIX)","(59,DUSTIN,TAUTOU)","(103,MATTHEW,LEIGH)","(181,MATTHEW,CARREY)"
AGENT TRUMAN               |{"(21,KIRSTEN,PALTROW)","(23,SANDRA,KILMER)","(62,JAYNE,NEESON)","(108,WARREN,NOLTE)",
AIRPLANE SIERRA            |{"(99,JIM,MOSTEL)","(133,RICHARD,PENN)","(162,OPRAH,KILMER)","(170,MENA,HOPPER)","(185
AIRPORT POLLOCK            |{"(55,FAY,KILMER)","(96,GENE,WILLIS)","(110,SUSAN,DAVIS)","(138,LUCILLE,DEE)"}        
ALABAMA DEVIL              |{"(10,CHRISTIAN,GABLE)","(22,ELVIS,MARX)","(26,RIP,CRAWFORD)","(53,MENA,TEMPLE)","(68,

Now, the query on Stack Overflow was, learn how to filter this outcome by whether or not the ARRAY (or MULTISET) accommodates a particular worth.

Filtering the ARRAY

We will’t simply add a WHERE clause to the question. Due to the logical order of operations in SQL, the WHERE clause “occurs earlier than” the SELECT clause, so the ARRAY just isn’t but accessible to WHERE. We might, nevertheless, wrap the whole lot in a derived desk and do that, as an alternative:

SELECT *
FROM (
  SELECT
    f.title,
    ARRAY(
      SELECT ROW(
        a.actor_id,
        a.first_name,
        a.last_name
      )
      FROM actor AS a
      JOIN film_actor AS fa USING (actor_id)
      WHERE fa.film_id = f.film_id
      ORDER BY a.actor_id
    ) AS actors
  FROM movie AS f
) AS f
WHERE actors @> ARRAY[(
  SELECT ROW(a.actor_id, a.first_name, a.last_name)
  FROM actor AS a 
  WHERE a.actor_id = 1
)]
ORDER BY f.title

Excuse the unwieldy ARRAY @> ARRAY operator. I’m not conscious of a greater strategy right here, as a result of it’s exhausting to unnest a structurally typed RECORD[] array in PostgreSQL, if we don’t use a nominal sort (CREATE TYPE ...). If you realize a greater method to filter, please let me know within the feedback part. Right here’s a greater model:

SELECT *
FROM (
  SELECT
    f.title,
    ARRAY(
      SELECT ROW(
        a.actor_id,
        a.first_name,
        a.last_name
      )
      FROM actor AS a
      JOIN film_actor AS fa USING (actor_id)
      WHERE fa.film_id = f.film_id
      ORDER BY a.actor_id
    ) AS actors
  FROM movie AS f
) AS f
WHERE EXISTS (
  SELECT 1 
  FROM unnest(actors) AS t (a bigint, b textual content, c textual content) 
  WHERE a = 1
)
ORDER BY f.title

Anyway, this produces the specified outcome:

title                |actors                                                                                           
---------------------+-------------------------------------------------------------------------------------------------
ACADEMY DINOSAUR     |{"(1,PENELOPE,GUINESS)","(10,CHRISTIAN,GABLE)","(20,LUCILLE,TRACY)","(30,SANDRA,PECK)","(40,JOHNN
ANACONDA CONFESSIONS |{"(1,PENELOPE,GUINESS)","(4,JENNIFER,DAVIS)","(22,ELVIS,MARX)","(150,JAYNE,NOLTE)","(164,HUMPHREY
ANGELS LIFE          |{"(1,PENELOPE,GUINESS)","(4,JENNIFER,DAVIS)","(7,GRACE,MOSTEL)","(47,JULIA,BARRYMORE)","(91,CHRIS
BULWORTH COMMANDMENTS|{"(1,PENELOPE,GUINESS)","(65,ANGELA,HUDSON)","(124,SCARLETT,BENING)","(173,ALAN,DREYFUSS)"}      
CHEAPER CLYDE        |{"(1,PENELOPE,GUINESS)","(20,LUCILLE,TRACY)"}                                                    
COLOR PHILADELPHIA   |{"(1,PENELOPE,GUINESS)","(106,GROUCHO,DUNST)","(122,SALMA,NOLTE)","(129,DARYL,CRAWFORD)","(163,CH
ELEPHANT TROJAN      |{"(1,PENELOPE,GUINESS)","(24,CAMERON,STREEP)","(37,VAL,BOLGER)","(107,GINA,DEGENERES)","(115,HARR
GLEAMING JAWBREAKER  |{"(1,PENELOPE,GUINESS)","(66,MARY,TANDY)","(125,ALBERT,NOLTE)","(143,RIVER,DEAN)","(155,IAN,TANDY

Now, all the outcomes are assured to be movies by which 'PENELOPE GUINESS' was an ACTOR. However is there a greater answer?

Utilizing ARRAY_AGG as an alternative

Nonetheless, in native PostgreSQL, it will be higher (on this case) to make use of ARRAY_AGG, I believe:

SELECT
  f.title,
  ARRAY_AGG(ROW(
    a.actor_id,
    a.first_name,
    a.last_name
  ) ORDER BY a.actor_id) AS actors
FROM movie AS f
JOIN film_actor AS fa USING (film_id)
JOIN actor AS a USING (actor_id)
GROUP BY f.title
HAVING bool_or(true) FILTER (WHERE a.actor_id = 1)
ORDER BY f.title

This produces the very same outcome:

title                |actors                                                                                          
---------------------+------------------------------------------------------------------------------------------------
ACADEMY DINOSAUR     |{"(1,PENELOPE,GUINESS)","(10,CHRISTIAN,GABLE)","(20,LUCILLE,TRACY)","(30,SANDRA,PECK)","(40,JOHN
ANACONDA CONFESSIONS |{"(1,PENELOPE,GUINESS)","(4,JENNIFER,DAVIS)","(22,ELVIS,MARX)","(150,JAYNE,NOLTE)","(164,HUMPHRE
ANGELS LIFE          |{"(1,PENELOPE,GUINESS)","(4,JENNIFER,DAVIS)","(7,GRACE,MOSTEL)","(47,JULIA,BARRYMORE)","(91,CHRI
BULWORTH COMMANDMENTS|{"(1,PENELOPE,GUINESS)","(65,ANGELA,HUDSON)","(124,SCARLETT,BENING)","(173,ALAN,DREYFUSS)"}     
CHEAPER CLYDE        |{"(1,PENELOPE,GUINESS)","(20,LUCILLE,TRACY)"}                                                   
COLOR PHILADELPHIA   |{"(1,PENELOPE,GUINESS)","(106,GROUCHO,DUNST)","(122,SALMA,NOLTE)","(129,DARYL,CRAWFORD)","(163,C
ELEPHANT TROJAN      |{"(1,PENELOPE,GUINESS)","(24,CAMERON,STREEP)","(37,VAL,BOLGER)","(107,GINA,DEGENERES)","(115,HAR
GLEAMING JAWBREAKER  |{"(1,PENELOPE,GUINESS)","(66,MARY,TANDY)","(125,ALBERT,NOLTE)","(143,RIVER,DEAN)","(155,IAN,TAND

How does it work?

• We’re grouping by FILM and combination the contents per movie right into a nested assortment.
• We will now use HAVING to filter on teams.
• BOOL_OR(TRUE) is TRUE as quickly because the GROUP is non-empty
• FILTER (WHERE a.actor_id = 1) was that filter standards, which we place within the group

So, the HAVING predicate is TRUE if there may be not less than one ACTOR_ID = 1, or NULL in any other case, which has the identical impact as FALSE. In the event you’re a purist, wrap the predicate in COALESCE(BOOL_OR(...), FALSE)

Intelligent or neat, or a little bit of each?

Doing this with jOOQ

Right here’s the jOOQ model, that works on any RDBMS that helps MULTISET_AGG (ARRAY_AGG emulation continues to be pending):

ctx.choose(
        FILM_ACTOR.movie().TITLE,
        multisetAgg(
            FILM_ACTOR.actor().ACTOR_ID,
            FILM_ACTOR.actor().FIRST_NAME,
            FILM_ACTOR.actor().LAST_NAME))
   .from(FILM_ACTOR)
   .groupBy(FILM_ACTOR.movie().TITLE)
   .having(boolOr(trueCondition())
       .filterWhere(FILM_ACTOR.actor().ACTOR_ID.eq(1)))
   .orderBy(FILM_ACTOR.movie().TITLE)
   .fetch();

Whereas the highly effective MULTISET worth constructor will get a lot of the fame with jOOQ customers, let’s not neglect there may be additionally a barely much less highly effective, however sometimes actually helpful MULTISET_AGG combination perform, which can be utilized for aggregations or as a window perform!