如何在Postgres 9.4 JSONB列中执行不区分大小写的搜索? [英] how do I perform a case-insensitive search in a Postgres 9.4 JSONB column?

问题描述

我正在使用此查询在其中profile是JSONB列的表中查找数据 而且只有在名称正确的情况下它才有效

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 ?

类似于"Super%"或"super user"

推荐答案

我找到了解决问题的方法:

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.

这篇关于如何在Postgres 9.4 JSONB列中执行不区分大小写的搜索?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！