From: | Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Steve Baldwin <steve(dot)baldwin(at)gmail(dot)com>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, Rob Sargent <robjsargent(at)gmail(dot)com>, "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Extracting data from jsonb array? |
Date: | 2020-12-08 06:16:42 |
Message-ID: | CAD3a31UBuSjXoLevQwUXnGL=QN=gcsy-Q8G6zNx=fcns14Omsw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Dec 7, 2020 at 9:54 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> writes:
> > There's one last piece of this query I'm clearly not getting though.
> Where
> > it says:
>
> > from foo as f, jsonb_to_recordset(js) as t(key2 text)
>
> > what is actually going on there? I keep reading this as a table foo (f)
> > cross-joined to a table created by jsonb_to_recordset (t). But that
> > doesn't seem right, because rows from t are only joining with matching
> rows
> > from f, rather than all of them. Is there some unspoken implicit logic
> > going on here, or something else entirely that is going over my head?
>
> There's an implicit LATERAL there:
>
> ... from foo as f, lateral jsonb_to_recordset(js) as t(key2 text)
>
> ie, for each row of foo, extract the foo.js column and evaluate
> jsonb_to_recordset(js) --- which, in this case, produces multiple
> rows that are joined to the original foo row. This is again a
> SQL-ism. I don't particularly care for their choice to allow
> LATERAL to be implicit for function-call-like FROM items,
> because it seems pretty confusing; but the spec is the spec.
>
>
That's (finally!) making sense to me.
> [ thinks for a bit... ] Again, I'm too lazy to go digging in
> the spec's dense verbiage at this hour, but I'm vaguely recalling
> that they may only require this behavior for the one case of
> the function being UNNEST(). I think it was our choice to allow
> it to work like that for any set-returning function.
>
>
The SELECT page David pointed me towards has a little section that seems to
confirm your recollection:
Function Calls in FROM
PostgreSQL allows a function call to be written directly as a member of the
FROM list. In the SQL standard it would be necessary to wrap such a
function call in a sub-SELECT; that is, the syntax FROM func(...) alias is
approximately equivalent to FROM LATERAL (SELECT func(...)) alias. *Note
that LATERAL is considered to be implicit; this is because the standard
requires LATERAL semantics for an UNNEST() item
in FROM. PostgreSQL treats UNNEST() the same as other set-returning
functions.*
Cheers,
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
From | Date | Subject | |
---|---|---|---|
Next Message | Aravindhan Krishnan | 2020-12-08 08:18:33 | Re: postgres-10 with FIPS |
Previous Message | Tom Lane | 2020-12-08 05:54:06 | Re: Extracting data from jsonb array? |