From: | Dmitry Dolgov <9erthalion6(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | reader 1001 <007reader(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Merlin Moncure <mmoncure(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 |
Date: | 2018-05-16 07:12:19 |
Message-ID: | CA+q6zcXMdzhhGCwK4KJPkhQp24rNO8ra-TNM=Zk2GtGNTKy09A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
> On 16 May 2018 at 05:59, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Tuesday, May 15, 2018, reader 1001 <007reader(at)gmail(dot)com> wrote:
>>
>> Yes, I realized it by now, thank you.
>> 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?
>> {
>> key1:value1,
>> key2: {
>> key3:value3},
>> key4:[
>> {
>> key5:value5
>> },
>> {
>> key6:value6
>> }
>> ]
>> }
>>
>
> 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. I have this
simple example, it should be easy to adapt it for your case (although
the value extraction part looks a bit cumbersome):
create type key1 as (a text);
create type key2 as (b key1);
create type key3 as (c key2);
create type key4 as (d key3, e text);
select (((d).c).b).a, e
from jsonb_populate_record(null::key4,
'{"d": {"c": {"b": {"a": "nested"}}}, "e": "test"}');
a | e
--------+------
nested | test
(1 row)
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2018-05-16 09:29:25 | BUG #15198: nextval() accepts tables/indexes when adding a default to a column |
Previous Message | David G. Johnston | 2018-05-16 03:59:25 | Re: Abnormal JSON query performance |