Search code examples
jsonpostgresqlpostgresql-9.4jsonb

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

Setup (PostgreSQL 9.4+)

Assume I have a table product:

create table product
(
    attributes jsonb
);

with data:

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

Question

How do I select all records' color attribute in PostgreSQL 9.4+? Since the keys differ in casing, I am unable to use this syntax:

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

My expected output would be:

Red
White
Blue

Possible Solution

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

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

Is this possible? I can see that it might conflict should you have color and Color keys on the same object, so I would not be surprised if this is not a thing.

References:

Solution

  • You should extract the pairs (key, value) to use the function lower() 

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

    or using a more verbose syntax:

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

    This cross join is a lateral join, the function jsonb_each() is executed once for each row from product.