Обсуждение: json_to_record Example
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! Best regards, Yoshi
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: https://www.postgresql.org/message-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 +
Вложения
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:
https://www.postgresql.org/message-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
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: > > https://www.postgresql.org/message-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:
>
> https://www.postgresql.org/message-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 +
Yousof Sagr Shaladi
Services Engineering
Denodo Technologies