Search Postgresql Archives

Re: How to search a string inside a json structure

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Nov 3, 2015 at 9:57 AM, Vick Khera <vivek@xxxxxxxxx> wrote:
>
> On Tue, Nov 3, 2015 at 10:07 AM, Sami Pietilä <sami.pietila@xxxxxxxxx>
> wrote:
>>
>> Unfortunately I could not figure out how to select rows which, for
>> example, contain following json: '{"a":"world","c":{"b":"helloworld"}}' by
>> search with "hello" string.
>
> cast the field to a text:
>
> select * from t where myfield::text like '%hello%';

Performance of this will be awful.  Unfortunately, FWICT there is no
way to do partial string matches against json in a indexed way without
some serious elbow grease.  For full key-value matching though you're
good to go.

Do index this operation, the best option today will revolve around the
pg_trgm module.  It can optimize like expressions against text.
Performance of this is a mixed bag although upcoming pg_trgm
enhancements should make it a lot faster.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux