Обсуждение: 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 +

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 +