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?
> On 14 May 2018 at 23:37, 007reader <007reader@gmail.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)
