Re: How to search a string inside a json structure

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