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

From 007reader
Subject Re: Abnormal JSON query performance
Date
Msg-id 5afc6f36.1c69fb81.65fd5.c29c@mx.google.com
Whole thread Raw
In response to Re: Abnormal JSON query performance  (Dmitry Dolgov <9erthalion6@gmail.com>)
List pgsql-bugs
It works, but not too practical. Users choose JSON for reasons of flexibility, so it's impossible to predict all queries upfront. I found a limitation as well- I tried to create new type, run query and drop the types all within a single transaction, but turned out I can't create new type within a transaction (begin ... end;).

-------- Original message --------
From: Dmitry Dolgov <9erthalion6@gmail.com>
Date: 5/16/18 12:12 AM (GMT-08:00)
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: reader 1001 <007reader@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, Merlin Moncure <mmoncure@gmail.com>, Pavel Stehule <pavel.stehule@gmail.com>, pgsql-bugs <pgsql-bugs@postgresql.org>
Subject: Re: Abnormal JSON query performance

> 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:
>>
>> 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)

pgsql-bugs by date:

Previous
From: Rick Gentry
Date:
Subject: Re: "order by" and "order by asc" returning different results on date field
Next
From: 007reader
Date:
Subject: Re: Abnormal JSON query performance