If I prefer - >> or @>

Question

In postgreSQL in a JSONB column, I can run two queries with the same (?) Result.

Request 1:

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

Request 2:

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

In terms of performance and semantics (and perhaps some other considerations I don't see here), what query should I use to search for "elements from awhere cthere are lorem"?

Answer 1

Depending on which indexes you have or want to add (if you want to use the index at all). And what other queries do you want to execute in the column jsonb.

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

() (b->>'c'),

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

GIN (b), .

, , , . b , GIN .

, .

Sidenote: NULL s:

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

, , c JSON NULL,

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

, c JSON NULL , c .

,

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

- - NULL ( (b ->> 'c') = NULL NULL - UNKNOWN BOOLEAN - WHERE).

Answer 2

, , . .

, . DO PL/pgSQL, 100000 , psql \timing - , , . , , . , , .

Answer 3

The same idea came up as Lorentz. And, despite the number of high key-value pairs, they seem to do the exact same thing:

 t=> do
t-> $$
t$> declare
t$>   _i int;
t$>   _j jsonb;
t$> begin
t$>   with n as (select generate_series(1,9999,1) g) select concat('{',string_agg(concat('"a',g,'":22'),','),',"c": "lorem"}')::jsonb into _j from n;
t$>   for _r in 1..999999 loop
t$>     select 1 into _i where _j @> '{"c": "lorem"}';
t$>   end loop;
t$> end;
t$> $$
t-> ;
DO
Time: 2406.016 ms
t=>
t=> do
t-> $$
t$> declare
t$>   _i int;
t$>   _j jsonb;
t$> begin
t$>   with n as (select generate_series(1,9999,1) g) select concat('{',string_agg(concat('"a',g,'":22'),','),',"c": "lorem"}')::jsonb into _j from n;
t$>   for _r in 1..999999 loop
t$>     select 1 into _i where _j->>'c' = 'lorem';
t$>   end loop;
t$> end;
t$> $$
t-> ;
DO
Time: 2799.750 ms