Re: How to search a string inside a json structure

On Tue, Nov 3, 2015 at 9:57 AM, Vick Khera <vivek@khera.org> wrote:
>
> On Tue, Nov 3, 2015 at 10:07 AM, Sami Pietilä <sami.pietila@gmail.com>
> 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