Postgres Pro
Downloads
Home   >   mailing lists

Re: Abnormal JSON query performance - Mailing list pgsql-bugs

From 007reader
Subject Re: Abnormal JSON query performance
Date
Msg-id 5afa017f.1c69fb81.75016.73f5@mx.google.com
Whole thread Raw
In response to Re: Abnormal JSON query performance  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: Abnormal JSON query performance  (Dmitry Dolgov <9erthalion6@gmail.com>)
List pgsql-bugs
Tree view
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.g.johnston@gmail.com>
Date: 5/14/18 9:09 AM (GMT-08:00)
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Merlin Moncure <mmoncure@gmail.com>, reader 1001 <007reader@gmail.com>, Pavel Stehule <pavel.stehule@gmail.com>, pgsql-bugs <pgsql-bugs@postgresql.org>
Subject: Re: Abnormal JSON query performance

On Mon, May 14, 2018 at 8:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, May 14, 2018 at 7:49 AM, Tom Lane <tgl@sss.pgh.pa.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.

pgsql-bugs by date:

Previous
From: Gavin Flower
Date:
Subject: Re: "REVOKE ... ON DATABASE template1 ..." has no effect
Next
From: chenhj
Date:
Subject: Re:Re: BUG #15187: When use huge page, there may be a lot of hangedconnections with status startup or authentication