example of json_to_record(json) not working

Started by Sébastien DELOBELover 7 years ago3 messagesbugs
Jump to latest
#1Sébastien DELOBEL
sdelobel@hotmail.com

Hi,

When you execute the example of the function "json_to_record(json)" in the documentation https://www.postgresql.org/docs/10/static/functions-json.html

PostgreSQL: Documentation: 10: 9.15. JSON Functions and ...<https://www.postgresql.org/docs/10/static/functions-json.html&gt;
www.postgresql.org
Operator Right Operand Type Description Example Example Result-> int: Get JSON array element (indexed from zero, negative integers count from the end)

select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype)

the expected result is :

a | b | c | d | r
---+---------+---------+---+---------------
1 | [1,2,3] | {1,2,3} | | (123,"a b c")

But in my cluster PostgreSQL 9.5.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

I have this result :

ERROR: type "myrowtype" does not exist
LINE 1: ...a b c"}}') as x(a int, b text, c int[], d text, r myrowtype)...
^

i think that query should be as follows to have the expected result:

select * from json_to_record('{"a":1,"b":[1,2,3],"c":"{4,5,6}","e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r json)

a | b | c | d | r
---+---------+---------+---+--------------------------
1 | [1,2,3] | {4,5,6} | | {"a": 123, "b": "a b c"}
(1 row)

Thanks you :)

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Sébastien DELOBEL (#1)
Re: example of json_to_record(json) not working

=?iso-8859-1?Q?S=E9bastien_DELOBEL?= <sdelobel@hotmail.com> writes:

When you execute the example of the function "json_to_record(json)" in the documentation https://www.postgresql.org/docs/10/static/functions-json.html
...
I have this result :
ERROR: type "myrowtype" does not exist

That example, and several others on the same page, assume that you've
created a suitable user-defined composite type "myrowtype". I don't think
removing that aspect of the example would be an improvement.

regards, tom lane

#3David G. Johnston
david.g.johnston@gmail.com
In reply to: Sébastien DELOBEL (#1)
Re: example of json_to_record(json) not working

On Monday, September 3, 2018, Sébastien DELOBEL <sdelobel@hotmail.com>
wrote:

This

(123,"a b c")

Is not the same thing as this

{"a": 123, "b": "a b c"}

The record stuff requires a reference type to convert the json stuff too.
The developer is responsible for ensuring a corresponding type or table
(implicit type) exists.

David J.