Re: Abnormal JSON query performance

> On 14 May 2018 at 23:37, 007reader <007reader(at)gmail(dot)com> wrote:
> 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?

Just to mention about #1. If I understand you correctly, it's not necessary to
define a rowtype for the entire doc, you can do this only for a part
that you want to extract from the document. It's also fine to have
some keys missing:

create type test as (a integer, b text, c text);
select * from json_populate_record(null::test, '{"a": 1, "b":
"test", "d": "test2"}');

a | b | c
---+------+------
1 | test | NULL
(1 row)