Re: Extracting data from jsonb array?

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.

[ 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.

regards, tom lane