Dmitry Dolgov <9erthalion6@gmail.com> writes: >> On 16 May 2018 at 05:59, David G. Johnston <david.g.johnston@gmail.com> wrote: >> On Tuesday, May 15, 2018, reader 1001 <007reader@gmail.com> wrote: >>> My question remains for hierarchical keys in a JSON document. If I have a >>> document like below, I clearly can extract key1 using the described rowtype >>> definition. How can I specify selected keys deeper in the document, e.g. >>> key3 and key5?
>> I believe you would need a type for each subtree and apply the function >> multiple times with the result of one feeding the next.
> Yes, you need to defined a type for each subtree, but as far as I can > tell it's not necessary to apply the function multiple times, > `jsonb_populate_record` can work with nested types, so it's enough > just to have every new type included in the previous one.
FWIW, I really doubt that there's much performance win from going further than the first-level keys. I suspect most of the cost that the OP is seeing comes from fetching the large JSONB document out of toast storage multiple times. Fetching it just in a single jsonb_populate_record() call will fix that. So I'd just return the top-level field(s) as jsonb column(s) and use the normal -> or ->> operators to go further down.
The vague ideas that I've had about fixing this type of problem automatically mostly center around detecting the need for duplicate toast fetches and doing that just once. For data types having "expanded" forms, it's tempting to consider also expanding them during the fetch, but that's less clearly a win.
Just note. If SQL/JSON will be implemented, then this discussion is useless, because JSON_TABLE function allows to read more values per one call.
