Use json_populate_recordset in a case-insensitive way

Question

Is it possible to use json_populate_recordset so that the table column names/json keys are compared in an case-insensitive way, using PostgreSQL (9.6)?

For example, the following snippet would return zero row.

CREATE TABLE foo (bar TEXT);
SELECT * from json_populate_recordset(null::foo, '[{"bAr":1}]')

Of course I could transform json keys to lowercase or the table name could be case sensitive.

Answer 1

I don't believe case-insensitive is possible. If you know in advance the case that will be used for records (e.g. they are always camel cased) you can specify a specific case by quoting the column name.

Baseline example to show case-insensitivity:

# create type x as (abc integer);
CREATE TYPE
# select * from json_populate_recordset(null::x, '[{"abc" : 1}, {"Abc" : 2}, {"aBc" : 3}, {"abC" : 4}]');
 abc
-----
   1



(4 rows)

Now let's choose a specific case we want to use by quoting the column name.

# drop type x;
DROP TYPE
# create type x as ("aBc" integer);
CREATE TYPE
edgar=# select * from json_populate_recordset(null::x, '[{"abc" : 1}, {"Abc" : 2}, {"aBc" : 3}, {"abC" : 4}]');
 aBc
-----


   3

(4 rows)

If you can't guarantee the case of your input data you show lower-case everything.