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