How to select case-insensitive JSONB keys in PostgreSQL (9.4+)

Question

Customization (PostgreSQL 9.4 +)

Suppose I have a product table:

 create table product ( attributes jsonb ); 

with data:

 insert into product (attributes) values ('{"Color": "Red"}'), ('{"color": "White"}'), ('{"COLOR": "Blue"}'); 

Question

How to select the color attribute of all records in PostgreSQL 9.4+? Since the keys are different in the enclosure, I cannot use this syntax:

 select attributes->>'color' as color from product; 

My expected result:

 Red White Blue 

Possible Solution

I also tried using this syntax (works, but feels hacked):

 select coalesce( attributes->>'color', attributes->>'Color', attributes->>'COLOR') as color from product; 

Is it possible? I see that this can lead to conflict if you have the color and color keys on the same object, so I won’t be surprised if it is not.

Literature:

• PostgreSQL JSON Functions and Operators

Answer 1

You must extract the pairs (key, value) in order to use the lower() function

 select value as color from product, jsonb_each(attributes) where lower(key) = 'color';