json_to_record Example

On Mon, May 7, 2018 at 06:14:02PM +0000, PG Doc comments form wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/10/static/functions-json.html
Description:

Hi team,

I had the following issue when going through your
https://www.postgresql.org/docs/current/static/functions-json.html docs.

Looking at the json_to_record example it took me quite a while that it is
not possible to put the json_to_record function right after the the from
clause but instead I would need to put the tables name in front, then use
the json_to_record function. Then put the column definitions behind it and
in the SELECT clause I need to query the columns using the alias. As you use
a * in your examples, I assumed that json_to_record returns all values found
in the json argument of that function.

As an idea I would suggest to provide a sample json which contains key-value
pairs as well as arrays and use this for the whole examples as someone would
rather not query a json written by hand.

Thank you very much and keep up the good work! I hope you understand and
like my suggestion!

I think you have a good point. I was confused too and it took me a
while to get it straight. The simplest example I could create is:

CREATE TABLE test(x INT, y JSONB);

INSERT INTO test VALUES (DEFAULT, '{"a":2,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}');

SELECT x.* FROM test, jsonb_to_record(y) AS x(a int, b text, c int[], d text);
a | b | c | d
---+-----------+---------+---
2 | [1, 2, 3] | {1,2,3} |

While we could add this to the docs, I prefer some text that explains
how to use this, and perhaps why.

The benefits of jsonb_to_record and friends compared to typical ->
JSON[B] indexing was outlined in this thread, and I am CC'ing the author
in this thread:

/messages/by-id/C3E7372D-153D-4276-8DB5-0D232ECD91E4@gmail.com

I have developed the attached doc patch which explains how to use
jsonb_to_record using a lateral reference (though the LATERAL keyword is
optional for function calls in Postgres), and a suggestion of the
performance benefits of using it. I feel text is really required to
accomplish all this, rather than an example.

Comments?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Hi,

Form my side I definitely agree with what you say and added. I think the
example makes it more clear as we have an example table used.

Nothing to add from my side here.

Best regards,

On Sat, May 26, 2018 at 5:03 PM Bruce Momjian <bruce@momjian.us> wrote:

On Mon, May 7, 2018 at 06:14:02PM +0000, PG Doc comments form wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/10/static/functions-json.html
Description:

Hi team,

I had the following issue when going through your
https://www.postgresql.org/docs/current/static/functions-json.html docs.

Looking at the json_to_record example it took me quite a while that it is
not possible to put the json_to_record function right after the the from
clause but instead I would need to put the tables name in front, then use
the json_to_record function. Then put the column definitions behind it

and

in the SELECT clause I need to query the columns using the alias. As you

use

a * in your examples, I assumed that json_to_record returns all values

found

in the json argument of that function.

As an idea I would suggest to provide a sample json which contains

key-value

pairs as well as arrays and use this for the whole examples as someone

would

rather not query a json written by hand.

Thank you very much and keep up the good work! I hope you understand and
like my suggestion!

I think you have a good point. I was confused too and it took me a
while to get it straight. The simplest example I could create is:

CREATE TABLE test(x INT, y JSONB);

INSERT INTO test VALUES (DEFAULT,
'{"a":2,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}');

SELECT x.* FROM test, jsonb_to_record(y) AS x(a int, b text, c
int[], d text);
a | b | c | d
---+-----------+---------+---
2 | [1, 2, 3] | {1,2,3} |

While we could add this to the docs, I prefer some text that explains
how to use this, and perhaps why.

The benefits of jsonb_to_record and friends compared to typical ->
JSON[B] indexing was outlined in this thread, and I am CC'ing the author
in this thread:

/messages/by-id/C3E7372D-153D-4276-8DB5-0D232ECD91E4@gmail.com

I have developed the attached doc patch which explains how to use
jsonb_to_record using a lateral reference (though the LATERAL keyword is
optional for function calls in Postgres), and a suggestion of the
performance benefits of using it. I feel text is really required to
accomplish all this, rather than an example.

Comments?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

--

Yousof Sagr Shaladi

Services Engineering

Denodo Technologies

+49 (0) 89 599 904 50 <+49%2089%2059990450>

yshaladi@denodo.com

www.denodo.com

On Sat, Jun 2, 2018 at 03:23:32PM +0200, Yousof Shaladi wrote:

Hi,

Form my side I definitely agree with what you say and added. I think the
example makes it more clear as we have an example table used.

Nothing to add from my side here.

Patch applied back through 9.4. Thanks.

---------------------------------------------------------------------------

Best regards,

On Sat, May 26, 2018 at 5:03 PM Bruce Momjian <bruce@momjian.us> wrote:

On Mon, May� 7, 2018 at 06:14:02PM +0000, PG Doc comments form wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/10/static/functions-json.html
Description:

Hi team,

I had the following issue when going through your
https://www.postgresql.org/docs/current/static/functions-json.html docs.

Looking at the json_to_record example it took me quite a while that it is
not possible to put the json_to_record function right after the the from
clause but instead I would need to put the tables name in front, then use
the json_to_record function. Then put the column definitions behind it

and

in the SELECT clause I need to query the columns using the alias. As you

use

a * in your examples, I assumed that json_to_record returns all values

found

in the json argument of that function.

As an idea I would suggest to provide a sample json which contains

key-value

pairs as well as arrays and use this for the whole examples as someone

would

rather not query a json written by hand.

Thank you very much and keep up the good work! I hope you understand and
like my suggestion!

I think you have a good point.� I was confused too and it took me a
while to get it straight.� The simplest example I could create is:

� � � � CREATE TABLE test(x INT, y JSONB);

� � � � INSERT INTO test VALUES (DEFAULT, '{"a":2,"b":[1,2,3],"c":
[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}');

� � � � SELECT x.* FROM test, jsonb_to_record(y) AS x(a int, b text, c int
[], d text);
� � � � �a |� � �b� � �|� � c� � | d
� � � � ---+-----------+---------+---
� � � � �2 | [1, 2, 3] | {1,2,3} |

While we could add this to the docs, I prefer some text that explains
how to use this, and perhaps why.

The benefits of jsonb_to_record and friends compared to typical ->
JSON[B] indexing was outlined in this thread, and I am CC'ing the author
in this thread:

� � � � /messages/by-id/flat/
C3E7372D-153D-4276-8DB5-0D232ECD91E4%40gmail.com

I have developed the attached doc patch which explains how to use
jsonb_to_record using a lateral reference (though the LATERAL keyword is
optional for function calls in Postgres), and a suggestion of the
performance benefits of using it.� I feel text is really required to
accomplish all this, rather than an example.

Comments?

--
� Bruce Momjian� <bruce@momjian.us>� � � � http://momjian.us
� EnterpriseDB� � � � � � � � � � � � � � �http://enterprisedb.com

+ As you are, so once was I.� As I am, so you will be. +
+� � � � � � � � � � � Ancient Roman grave inscription +

--

Yousof Sagr Shaladi

Services Engineering

Denodo Technologies

+49 (0) 89 599 904 50

yshaladi@denodo.com

www.denodo.com

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Thank you for hearing me out!

On Tue, Jun 19, 2018 at 7:43 PM Bruce Momjian <bruce@momjian.us> wrote:

On Sat, Jun 2, 2018 at 03:23:32PM +0200, Yousof Shaladi wrote:

Hi,

Form my side I definitely agree with what you say and added. I think the
example makes it more clear as we have an example table used.

Nothing to add from my side here.

Patch applied back through 9.4. Thanks.

---------------------------------------------------------------------------

Best regards,

On Sat, May 26, 2018 at 5:03 PM Bruce Momjian <bruce@momjian.us> wrote:

On Mon, May 7, 2018 at 06:14:02PM +0000, PG Doc comments form wrote:

The following documentation comment has been logged on the website:

Page:

https://www.postgresql.org/docs/10/static/functions-json.html

Description:

Hi team,

I had the following issue when going through your
https://www.postgresql.org/docs/current/static/functions-json.html

docs.

Looking at the json_to_record example it took me quite a while

that it is

not possible to put the json_to_record function right after the

the from

clause but instead I would need to put the tables name in front,

then use

the json_to_record function. Then put the column definitions

behind it

and

in the SELECT clause I need to query the columns using the alias.

As you

use

a * in your examples, I assumed that json_to_record returns all

values

found

in the json argument of that function.

As an idea I would suggest to provide a sample json which contains

key-value

pairs as well as arrays and use this for the whole examples as

someone

would

rather not query a json written by hand.

Thank you very much and keep up the good work! I hope you

understand and

like my suggestion!

I think you have a good point. I was confused too and it took me a
while to get it straight. The simplest example I could create is:

CREATE TABLE test(x INT, y JSONB);

INSERT INTO test VALUES (DEFAULT, '{"a":2,"b":[1,2,3],"c":
[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}');

SELECT x.* FROM test, jsonb_to_record(y) AS x(a int, b text,

c int

[], d text);
a | b | c | d
---+-----------+---------+---
2 | [1, 2, 3] | {1,2,3} |

While we could add this to the docs, I prefer some text that explains
how to use this, and perhaps why.

The benefits of jsonb_to_record and friends compared to typical ->
JSON[B] indexing was outlined in this thread, and I am CC'ing the

author

in this thread:

/messages/by-id/flat/
C3E7372D-153D-4276-8DB5-0D232ECD91E4%40gmail.com

I have developed the attached doc patch which explains how to use
jsonb_to_record using a lateral reference (though the LATERAL

keyword is

optional for function calls in Postgres), and a suggestion of the
performance benefits of using it. I feel text is really required to
accomplish all this, rather than an example.

Comments?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

--

Yousof Sagr Shaladi

Services Engineering

Denodo Technologies

+49 (0) 89 599 904 50 <+49%2089%2059990450>

yshaladi@denodo.com

www.denodo.com

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

--

Yousof Sagr Shaladi

Services Engineering

Denodo Technologies

+49 (0) 89 599 904 50 <+49%2089%2059990450>

yshaladi@denodo.com

www.denodo.com