Re: jsonb case insensitive search

[armand pirvu <armand.pirvu@xxxxxxxxx>]

On May 31, 2017, at 2:32 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Wed, May 31, 2017 at 12:18 PM, armand pirvu <armand.pirvu@xxxxxxxxx> 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