Skip to content
Advertisement

postgresql jsonb case insensitive query with index

I was looking for a way to make a case insensitive query, and I found it here (postgresql jsonb case insensitive query), more precisely with a query like this : select ... where upper(data::text)::jsonb @> upper('[{"city":"New York"}]')::jsonb

However, I can’t seem to find enough information about how to create an index to be used by such a query.

where data::text @> '[{"city":"New York"}]'

works perfectly with a GIN index

CREATE INDEX i1 ON mytable USING gin(data);

but the upper(…) function will trigger a full scan which costs too much.

Thank you in advance.

Advertisement

Answer

You need to index the complete expression:

CREATE INDEX ON tab USING gin ((upper(data::text)::jsonb));
Advertisement