Re: jsonb case insensitive search

> On May 31, 2017, at 2:32 PM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Wed, May 31, 2017 at 12:18 PM, armand pirvu <armand(dot)pirvu(at)gmail(dot)com <mailto:armand(dot)pirvu(at)gmail(dot)com>> wrote:
>
> For the example mentioned
>
> SELECT *
> FROM cfg_files_data
> WHERE cfg_files_data.show_id = 32
> AND cfg_files_data.file_id = 123
> AND lower((file_data_record ->> 'Company')) = lower('CompuTestSystems');
> ;
>
>
> create index cfg_files_data_record_idx on cfg_files_data (show_id, file_id, ​​lower(file_data_record::text));
>
>
> Not sure why the index is ignored
>
> Because ​"lower((file_data_record ->> 'Company'))" is not the same as ​"lower(file_data_record::text)”

I see, missed it

Cause this works
create index fooidx on cfg_files_data (show_id, file_id, lower(file_data_record ->> 'Company'));

The idea is that I would like to avoid having an index for each key possibly wanted to search, but rather say have the whole record then search by the key and get the key value, thus having one index serving multiple purposes so to speak

I looked at JSQuery but does not seem that I can have a composite index like fooidx ???!

>
>
> But is is possible to go for a broader search, aka being able to search by any key:value , efficient and case insensitive ? What am I missing in this picture ?
>
>
> Use a trigger to maintain an all lower case copy of the json file_data_record and use the copy for predicates while using the original ​for select-list outputs.
>
> David J.

Uhh that would be let’s just say less than optimal. But maybe JSON itself is not intended to be used this way ? Or the functionality is just not there yet ?

Thanks
Armand