PostgreSQL JSONB Array Row Must Exist in Passed in Array

Question

I am using a jsonb column to store features in an array (I followed @a_horse_with_no_name 's advise in Best Way to Index All JSONB Fields With Boolean Values in PostgreSQL).

Example:

feature
------------------------------
[ "happy", "motivated" ]
[ "happy", "fast", "strong" ]
[ "smart", "fast" ]
[ "happy" ]

SELECT * 
FROM mytable 
WHERE feature <@ '["happy"]';

should return only row 4.

SELECT * 
FROM mytable 
WHERE feature <@ '["happy", "motivated"]';

should return row 1 and row 4.

SELECT * 
FROM mytable 
WHERE feature <@ '["happy", "motivated", "fast", "strong", "smart"]';

should return all 4 rows.

I have the column GIN indexed. From what I understand ?| would be more performant, but I need to use <@ because the rows must exist in the array. ?| would only be able to find any key element from array in the rows, not the inclusion of each row in the array.

Any workarounds to use ?| vs the <@ syntax?

jjanes · Accepted Answer · 2019-12-12 03:00:07Z

1

If you used text[] rather than JSONB, then <@ would be indexable directly.

If you really need JSONB, then you could use ?| to get the index usage and <@ to rule out the false positives:

SELECT * FROM mytable WHERE feature ?| '{"happy", "motivated"}' feature <@ '["happy", "motivated"]';

One caveat is that the above will not return empty arrays, while just the <@ would.

answered Dec 12, 2019 at 3:00

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

2 Comments

PressingOnAlways Over a year ago

If I were to use text[], what is the proper index to use? Gin index? I did an explain and i am getting a Seq Scan. Seq Scan on mytable (cost=0.00..13.12 rows=1 width=296) Filter: (feature <@ '{happy,motivated}'::text[]).

jjanes Over a year ago

Correct. With a tiny table, it won't use the index because reading the whole table is faster. You either need a large table, or need to set enable_seqscan=off, to see the index scan.

Collectives™ on Stack Overflow

PostgreSQL JSONB Array Row Must Exist in Passed in Array

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related