how do I perform a case-insensitive search in a Postgres 9.4 JSONB column?

Question

i'm using this query to look for data in a table where profile is a JSONB column and it works but only if the name is exactly that

SELECT * FROM "users" WHERE "profile" @> '{"name":"Super User"}'

is it possible to have more flexibility like case insensitivity, wildcards and so on ?

Something like "Super%" or "super user"

To do that we'd need something like a json query language. There's nothing like that built-in, but some folks are working on a json query language extension. I can't find references right now, but it was discussed at PGConf EU 2014 in Madrid; maybe look at the conference session list. — Craig Ringer
are you referring to github.com/akorotkov/jsquery ? i'll try that — G3z
Yep, that's the one. It looked quite interesting. I don't know how stable/mature/documented/etc it is though. — Craig Ringer

G3z G3z · Accepted Answer · 2014-12-29T14:04:33

I found the solution to my problem:

SELECT * FROM "users" WHERE (profile #>> '{name}') ILIKE 'super %'

I don't know if this is performing well enough but it works.
Probably it's wise to add an index to it.