Re: Abnormal JSON query performance

It would be great to document jsonb_populate_record better, especially the rowtype. May be it is obvious to an experienced user, but for a less experienced it isn't clear how it should be defined. Only after Tom's email, I realized that it can be done without creating a table.
My use case may be a bit more complex:1. My JSON doc is large - few hundred keys and it is not practical to define rowtype for the entire doc. Plus not all docs have all keys in each record. I'd like to specify only a relatively small number of keys (by their path) for jsonb_populate_record instead of the entire json field. 2. My docs have hierarchical structure, but the output should be flattened base on the structure defined in #1.
Can those problems be addressed within the current implementation?

-------- Original message --------From: "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> Date: 5/14/18 9:09 AM (GMT-08:00) To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> Cc: Merlin Moncure <mmoncure(at)gmail(dot)com>, reader 1001 <007reader(at)gmail(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, pgsql-bugs <pgsql-bugs(at)postgresql(dot)org> Subject: Re: Abnormal JSON query performance
On Mon, May 14, 2018 at 8:53 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:

> On Mon, May 14, 2018 at 7:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>> SELECT ... FROM ...some tables...,

>> jsonb_to_record(jsonbcol) AS j(id int, name text, price numeric)

>> WHERE ...

>>

>> which is something you can do today.

> ​Indeed you can - could you please point to the docs for that one?

https://www.postgresql.org/docs/current/static/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE

​

​Thanks.  I was thinking you were still talking about "populate" variants of the functions and missed that you switched to the "to" variant in the final example.​ 
Perhaps it'd be worth emphasizing the usefulness of jsonb_to_record[set]

a bit more, say with examples in section 8.14.

A section titled "JSON Element Extraction" under 8.14 that covers those functions in context and discusses the dynamics of multiple columns of -> invocations seem worthwhile.
"Ideally, JSON documents should each represent an atomic datum that business rules dictate cannot reasonably be further subdivided into smaller datums that could be modified independently."

See that new section should you decide to not heed the above advice, and in general converting between json and table forms.
David J.