Taking a JSON table with some simple data:
CREATE TABLE jsonthings(d JSONB NOT NULL);
INSERT INTO jsonthings(d) VALUES('{"id":1,"email":"Test1@test.com"}');
INSERT INTO jsonthings(d) VALUES('{"id":2,"email":"Test2@test.com"}');
INSERT INTO jsonthings(d) VALUES('{"id":3,"email":"Test3@test.com"}');
I have a requirement to obtain the rows which case-insensitively match a set of email addresses:
SELECT d FROM jsonthings
WHERE (d->>'email')::CITEXT = ANY(ARRAY['TEST1@TEST.COM','test3@test.com']::CITEXT[]);
However, the data I return should contain the email addresses in the case in which they were passed to the query rather than the case in which they are stored within the database. For example, the above query provides the results:
{"id": 1, "email": "Test1@test.com"}
{"id": 3, "email": "Test3@test.com"}
But I need it to return the results:
{"id": 1, "email": "TEST1@TEST.COM"}
{"id": 3, "email": "test3@test.com"}
How can I obtain the matching item in the array and use it in the results?