Thread: BUG #13972: jsonb_to_record cant map camelcase keys

The following bug has been logged on the website:

Bug reference:      13972
Logged by:          Jacob Zneider
Email address:      zn@dbml.dk
PostgreSQL version: 9.4.5
Operating system:   OS x 10.11.3
Description:

Try the following:

select * from json_to_record('{"aA":1,"bB":[1,2,3],"c":"bar"}') as x(aA int,
bB text, c text)

first two columns will be empty.

select * from json_to_record('{"aa":1,"bb":[1,2,3],"c":"bar"}') as x(aA int,
bB text, c text)

All columns are mapped.

select * from json_to_record('{"aa":1,"bb":[1,2,3],"c":"bar"}') as x(aa int,
bb text, c text)

All columns are mapped.

On Thu, Feb 18, 2016 at 6:12 AM, <zn@dbml.dk> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13972
> Logged by:          Jacob Zneider
> Email address:      zn@dbml.dk
> PostgreSQL version: 9.4.5
> Operating system:   OS x 10.11.3
> Description:
>
> Try the following:
>
> select * from json_to_record('{"aA":1,"bB":[1,2,3],"c":"bar"}') as x(aA
> int,
> bB text, c text)
>
> first two columns will be empty.
>
> select * from json_to_record('{"aa":1,"bb":[1,2,3],"c":"bar"}') as x(aA
> int,
> bB text, c text)
>
> All columns are mapped.
>
> select * from json_to_record('{"aa":1,"bb":[1,2,3],"c":"bar"}') as x(aa
> int,
> bb text, c text)
>
>
=E2=80=8B
Working as intended.
=E2=80=8B


=E2=80=8BUnquoted identifiers in SQL are folded to lower case.  There is no
difference between the "as x(...)" in your example.  They all result in
(aa, bb, c)=E2=80=8B.  The fact that json_to_record attempts to match in a
case-sensitive manner is intentional.

David J.

zn@dbml.dk writes:
> Try the following:

> select * from json_to_record('{"aA":1,"bB":[1,2,3],"c":"bar"}') as x(aA int,
> bB text, c text)

> first two columns will be empty.

You need to do it like this:

select * from json_to_record('{"aA":1,"bB":[1,2,3],"c":"bar"}') as x("aA" int,
"bB" text, c text);
 aA |   bB    |  c
----+---------+-----
  1 | [1,2,3] | bar
(1 row)

Without the quotes, the SQL names aA etc are case-folded to aa etc.

            regards, tom lane