Re: How to search a string inside a json structure

[Sami Pietilä <sami.pietila@xxxxxxxxx>]

Hi,

Thank you for reply.

I think I need to do some more research of means to implement searching for json databases.

I'll look the module.

Thanks

2015-11-03 18:43 GMT+02:00 Merlin Moncure <mmoncure@xxxxxxxxx>:

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