Thread: json_to_recordset not working with camelcase json keys

Hi all,

We tried to extract from a json with camel case keys values, but unfortunat=
elly they are not mapped.

Testcase: (PostgreSQL 9.4.5)

select * from json_to_recordset('[{"aB":1,"bC":"foo"},{"aB":"2","cD":"bar"}=
]') as x(aB int, bC text);

Expected:
aB | bC
1   | foo
2   | null

Actual:
aB  | bC
null | null
null | null

Does anyone knows if this is a bug?
Thanks for any help.

Kind regards,
Daniel

--
Our software sets the course: http://nca.me/software

Daniel Kellenberger | daniel.kellenberger@netcetera.com | T +41 44 297 58 1=
4 |
Netcetera AG | 8040 Z?rich | Switzerland | http://netcetera.com |

On 12/15/2015 01:46 PM, Daniel Kellenberger wrote:
> We tried to extract from a json with camel case keys values, but
> unfortunatelly they are not mapped.
>=20
> Testcase: (PostgreSQL 9.4.5)
>=20
> select * from
> json_to_recordset('[{"aB":1,"bC":"foo"},{"aB":"2","cD":"bar"}]') as x(a=
B
> int, bC text);

Not a bug, I believe. You probably meant this:

select * from json_to_recordset('[{"aB":1,"bC":"foo"},
                                  {"aB":"2","cD":"bar"}]')
              as x("aB" int, "bC" text, "cD" text);
 aB | bC  | cD
----+-----+-----
  1 | foo |
  2 |     | bar
(2 rows)

Unquoted identifiers are lowercased in postgres, so for camel case
identifiers you would need double quotes.

HTH,

Joe

--=20
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Hi Joe=0A=
=0A=
Thanks for your answer, this solved our issue.=0A=
In one project we are currently switching from Oracle to PostgreSQL and are=
 excited about the database.=0A=
=0A=
Kind regards,=0A=
Daniel=0A=
=0A=
-- =0A=
Our software sets the course: http://nca.me/software=0A=
=0A=
Daniel Kellenberger | daniel.kellenberger@netcetera.com | T +41 44 297 58 1=
4 |=0A=
Netcetera AG | 8040 Z=FCrich | Switzerland | http://netcetera.com | =0A=
=0A=
________________________________________=0A=
Von: Joe Conway <mail@joeconway.com>=0A=
Gesendet: Mittwoch, 16. Dezember 2015 00:17=0A=
An: Daniel Kellenberger; pgsql-bugs@postgresql.org=0A=
Betreff: Re: [BUGS] json_to_recordset not working with camelcase json keys=
=0A=
=0A=
On 12/15/2015 01:46 PM, Daniel Kellenberger wrote:=0A=
> We tried to extract from a json with camel case keys values, but=0A=
> unfortunatelly they are not mapped.=0A=
>=0A=
> Testcase: (PostgreSQL 9.4.5)=0A=
>=0A=
> select * from=0A=
> json_to_recordset('[{"aB":1,"bC":"foo"},{"aB":"2","cD":"bar"}]') as x(aB=
=0A=
> int, bC text);=0A=
=0A=
Not a bug, I believe. You probably meant this:=0A=
=0A=
select * from json_to_recordset('[{"aB":1,"bC":"foo"},=0A=
                                  {"aB":"2","cD":"bar"}]')=0A=
              as x("aB" int, "bC" text, "cD" text);=0A=
 aB | bC  | cD=0A=
----+-----+-----=0A=
  1 | foo |=0A=
  2 |     | bar=0A=
(2 rows)=0A=
=0A=
Unquoted identifiers are lowercased in postgres, so for camel case=0A=
identifiers you would need double quotes.=0A=
=0A=
HTH,=0A=
=0A=
Joe=0A=
=0A=
--=0A=
Crunchy Data - http://crunchydata.com=0A=
PostgreSQL Support for Secure Enterprises=0A=
Consulting, Training, & Open Source Development=0A=
=0A=