Should I prefer ->> or @>

Question

In postgreSQL on a JSONB column I can run two queries with the same (?) result.

Query 1:

SELECT * FROM a WHERE b->>'c' = 'lorem';

Query 2:

SELECT * FROM a WHERE b @> '{"c": "lorem"}';

In term of performance and semantic (and maybe some other consideration I don't see here), what query should I use to search "the items from a where c is lorem"?

Answer 1

It depends on what indexes do you have or want to add (if you want to use an index at all). And what other queries you want to execute on the jsonb typed column.

WHERE b->>'c' = 'lorem'

queries will benefit from index(es) on the (b->>'c') expression, while

WHERE b @> '{"c": "lorem"}'

queries will benefit from a GIN index on the (b) column, but not the other ways around.

The first form will likely result in a smaller, and more effective index, but will only work for this special case. If you want to query other properties of b too, the GIN index can be more helpful.

If you don't want to use indexes at all, it's really just a matter of taste.

Sidenote: the solutions above work a little different, when dealing with NULLs:

WHERE b @> '{"c": null}'

will select rows if, and only if the c property has a JSON null value in it, while

WHERE (b ->> 'c') IS NULL

will select rows if the c property has a JSON null value in it, or the c property is not defined in the row at all.

Also,

WHERE (b ->> 'c') = NULL

won't select any rows, because of the standard compliant handling of NULLs (the expression (b ->> 'c') = NULL is always evaluated to NULL -- or UNKNOWN within the BOOLEAN type --, which is always falsy in the context of WHERE predicates).

Answer 2

It is a matter of taste which solution looks better to you, you be the judge there. They look about the same to me.

When it comes to performance, I suggest that you test it yourself. Write a DO statement in PL/pgSQL that performs the same operation 100000 times in a loop, then use the psql command \timing or something similar to see how long it takes. Repeat the tests several times to check if the result is reproducible. Chances are that the difference, if any, is lost in the noise.