[ad_1]
I stumbled upon a really fascinating query on Stack Overflow about tips on how to use jOOQ’s MULTISET
operator to nest a group, after which filter the consequence by whether or not that nested assortment incorporates a price.
The query is jOOQ particular, however think about, you may have a question that nests collections utilizing JSON in PostgreSQL. Assuming, as all the time, the Sakila database. Now, PostgreSQL doesn’t help the SQL normal MULTISET
operator, however we are able to use ARRAY
, which works nearly the identical manner.
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, tips on how to filter this consequence by whether or not the ARRAY
(or MULTISET
) incorporates a selected 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
will not be but out there to WHERE
. We may, nevertheless, wrap every thing 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 method right here, as a result of it’s arduous to unnest a structurally typed RECORD[]
array in PostgreSQL, if we don’t use a nominal kind (CREATE TYPE ...
). If you understand a greater strategy 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 consequence:
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 resolution?
Utilizing ARRAY_AGG as an alternative
Nevertheless, in native PostgreSQL, it could 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 consequence:
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)
isTRUE
as quickly because theGROUP
is non-emptyFILTER (WHERE a.actor_id = 1)
was that filter standards, which we place within the group
So, the HAVING
predicate is TRUE
if there’s not less than one ACTOR_ID = 1
, or NULL
in any other case, which has the identical impact as FALSE
. Should 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 overlook there’s additionally a barely much less highly effective, however often actually helpful MULTISET_AGG
combination operate, which can be utilized for aggregations or as a window operate!
[ad_2]