Re: json_to_record Example

Thank you for hearing me out!

On Tue, Jun 19, 2018 at 7:43 PM Bruce Momjian <bruce(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)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(at)denodo(dot)com
> >
> > www.denodo.com
> >
>
> --
> Bruce Momjian <bruce(at)momjian(dot)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(at)denodo(dot)com

www.denodo.com