CODEWITHSUNDEEP
postgresqljsonb
PressingOnAlways
PressingOnAlways

Reputation: 12356

PostgreSQL JSONB Array Row Must Exist in Passed in Array

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?

Upvotes: 0

Views: 43

Answers (1)

jjanes
jjanes

Reputation: 44137

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.

Upvotes: 1

Related Questions