Postgres @> Any Value

Question

I have a query that is checking the jsonb genre column and returning all movies that contain a passed genre number. As so:

SELECT id, name, year, description, "avgRating", "posterPath", genres, "trailerPath", seen 
FROM movies 
WHERE genres @> $1 
ORDER BY year desc

So if I pass '18' as $1 (the $1 is coming from a node module) an example row would be:

On the front end drop down have also included an 'ALL' choice that can be passed. Originally i just had my server detect the 'ALL' value and instead run a query for the entire table. The current implementation is unfortunately causing issues with combining filters for queries now, So my question is whether there is a value I can pass to the @> operator that would return movies with any genre or do I need to change the structure of my query?

--- edit pertaining to passing arrays to the query ---

Testing solution queries in pgAdmin 4 i pass the following:

SELECT id, name, year, description, "avgRating", "posterPath", genres, "trailerPath", seen FROM movies WHERE genres @> [18] ORDER BY year desc

And I get back the following error:

ERROR: syntax error at or near "[" LINE 3: WHERE genres @> [18] ^ SQL state: 42601 Character: 122

The arrow is intended to point at the opening array bracket point out the location of the error

Something like WHERE genres @> coalesce(nullif($1, 'ALL'), genres) should do it — Mike Organek
– Mike Organek, Commented Dec 1, 2020 at 21:21
@MikeOrganek That doesn't work for me. dbfiddle.uk/… Even when properly casting text to jsonb, it still fails. — Martin Burch
– Martin Burch, Commented Dec 1, 2020 at 21:33
@MartinBurch It works with the cast in this fiddle: dbfiddle.uk/… — Mike Organek
– Mike Organek, Commented Dec 1, 2020 at 21:58
Ah, that's great @MikeOrganek ! I think the cast I tried was in the wrong spot. Nice solution. Please post an answer :) — Martin Burch
– Martin Burch, Commented Dec 1, 2020 at 22:01

Martin Burch · Accepted Answer · 2020-12-01 21:30:03Z

1

The @> operator is the contains operator. You should pass an array [18] instead of a string 18. Then you could pass an empty array [] if no particular filter was set.

WHERE genres @> []

db<>fiddle example

answered Dec 1, 2020 at 21:30

Martin Burch

3,0104 gold badges34 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

iwantmyhatback Over a year ago

This is the behavior i had expected, however whenever i try to pass [18] i get a syntax error

Martin Burch Over a year ago

@iwantmyhatback how about WHERE genres @> '$1'? make sure the $1 substitution is being JSON.stringify()'d and that it contains an array of numbers, not an array of strings, to match the database. For example ["18"] won't work (at least in my example, anyway). If this persists, please edit the question to add your syntax error. There should be a hint if it's from postgres. What is your postgres version?

iwantmyhatback Over a year ago

psql (PostgreSQL) 12.4 -- i will have to try with stringify when I am home, right now i am just testing solutions by querying the DB directly in pgAdmin4.

Martin Burch Over a year ago

This certainly shouldn't be a problem in pgAdmin. My example works in Postgres 12 too. Perhaps posting your table CREATE and some rows from your INSERT would help us understand.

GMB · Accepted Answer · 2020-12-01 21:30:19Z

0

You could use boolean logic like so:

WHERE $1 = 'ALL' OR genres ? $1

Note that this uses ? instead of @>: the latter searches for a jsonb value in the jsonb array, while the former searches for a text value - which seems to be what you want.

answered Dec 1, 2020 at 21:30

GMB

224k25 gold badges103 silver badges151 bronze badges

1 Comment

Martin Burch Over a year ago

be careful and wrap this OR statement in parens if you add any other conditions to your where clause: WHERE ($1 = 'ALL' OR genres ? $1) AND year > $2 etc.

Collectives™ on Stack Overflow

Postgres @> Any Value

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related