Mario Tacke Mario Tacke - 4 months ago 35
JSON Question

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:


Answer

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

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