pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to create index on json array in postgres
4+ messages / 4 participants
[nested] [flat]
* How to create index on json array in postgres
@ 2019-01-01 05:49 Surya S <surya.s@citrusinformatics.com>
2019-01-04 17:10 ` Re: How to create index on json array in postgres Alexey Bashtanov <bashtanov@imap.cc>
0 siblings, 1 reply; 4+ messages in thread
From: Surya S @ 2019-01-01 05:49 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
Hi,
I have a json field called 'elements' in my table demo which
contains an array 'data' containing key value pairs. the 'data' array
has the below structure. the data array may have multiple json entries.I
am using postgres version 9.5
{ "data": [{ "ownr": "1", "siUsr": [2], "sigStat": "APPR", "modifiedOn":
1494229698039, "isDel": "false", "parentId": "nil", "disName":
"exmp.json", "uniqueId": "d88cb52", "usrType": "owner", "usrId": "1",
"createdOn": 1494229698039, "obType": "file" }] }
In my query I have multiple filters based on obj(Eg : obj->>usrId,
obj->>siUsr etc) where obj corresponds to
json_array_elements(demo.elements->'data').How do I create btree indices
on filters like obj->>userId ,obj->>sigUsr? Please revert.
Regards
Surya
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to create index on json array in postgres
2019-01-01 05:49 How to create index on json array in postgres Surya S <surya.s@citrusinformatics.com>
@ 2019-01-04 17:10 ` Alexey Bashtanov <bashtanov@imap.cc>
2019-01-04 20:52 ` Re: How to create index on json array in postgres Rene Romero Benavides <rene.romero.b@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Alexey Bashtanov @ 2019-01-04 17:10 UTC (permalink / raw)
To: Surya S <surya.s@citrusinformatics.com>; pgsql-sql@lists.postgresql.org
> I have a json field called 'elements' in my table demo which
> contains an array 'data' containing key value pairs. the 'data' array
> has the below structure. the data array may have multiple json
> entries.I am using postgres version 9.5
>
> { "data": [{ "ownr": "1", "siUsr": [2], "sigStat": "APPR",
> "modifiedOn": 1494229698039, "isDel": "false", "parentId": "nil",
> "disName": "exmp.json", "uniqueId": "d88cb52", "usrType": "owner",
> "usrId": "1", "createdOn": 1494229698039, "obType": "file" }] }
>
> In my query I have multiple filters based on obj(Eg : obj->>usrId,
> obj->>siUsr etc) where obj corresponds to
> json_array_elements(demo.elements->'data').How do I create btree
> indices on filters like obj->>userId ,obj->>sigUsr? Please revert.
>
I would maybe
1) make an immutable function called that extracts all user ids from
json as an array:
`create function extractUserIds(p_elements json) returns array as $$
select array(select ... from json_array_elements(p_elements->...)); $$ ...;`
2) create a functional gin or gist index : `create index ... on ...
using ... (extractUserIds(elements));`
3) use conditions like `where extractUserIds(elements) && array[...]`
Alternatively, I'd consider a schema redesign, as it looks like you may
benefit from a normalized schema.
Best,
Alex
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to create index on json array in postgres
2019-01-01 05:49 How to create index on json array in postgres Surya S <surya.s@citrusinformatics.com>
2019-01-04 17:10 ` Re: How to create index on json array in postgres Alexey Bashtanov <bashtanov@imap.cc>
@ 2019-01-04 20:52 ` Rene Romero Benavides <rene.romero.b@gmail.com>
2019-01-04 21:04 ` Re: How to create index on json array in postgres Steve Midgley <science@misuse.org>
0 siblings, 1 reply; 4+ messages in thread
From: Rene Romero Benavides @ 2019-01-04 20:52 UTC (permalink / raw)
To: Alexey Bashtanov <bashtanov@imap.cc>; +Cc: Surya S <surya.s@citrusinformatics.com>; pgsql-sql@lists.postgresql.org
What made you guys use a json array schema in the first place? is there a
requirement for storing highly variable fields or introducing/removing new
fields "on the fly"? IMHO, it's better a normalized approach if fields
don't vary that much for each record and you need to perform ad-hoc queries
in a performant manner, it would also make partitioning easier to implement
if needed.
How many records do you expect to have in this table? how do you expect it
to grow? what's the maximum array elements that each record could possibly
have? how are they going to be updated? frequently? rarely? are you
considering partitioning for this table?
If you really need the flexibility of the json data type, what about
creating independent fields for the common filters? you would be
duplicating information, but your indexes and queries would be less
complex.
On Fri, Jan 4, 2019 at 11:10 AM Alexey Bashtanov <bashtanov@imap.cc> wrote:
>
> I have a json field called 'elements' in my table demo which contains
> an array 'data' containing key value pairs. the 'data' array has the below
> structure. the data array may have multiple json entries.I am using
> postgres version 9.5
>
> { "data": [{ "ownr": "1", "siUsr": [2], "sigStat": "APPR", "modifiedOn":
> 1494229698039, "isDel": "false", "parentId": "nil", "disName": "exmp.json",
> "uniqueId": "d88cb52", "usrType": "owner", "usrId": "1", "createdOn":
> 1494229698039, "obType": "file" }] }
>
> In my query I have multiple filters based on obj(Eg : obj->>usrId,
> obj->>siUsr etc) where obj corresponds to
> json_array_elements(demo.elements->'data').How do I create btree indices on
> filters like obj->>userId ,obj->>sigUsr? Please revert.
>
>
> I would maybe
> 1) make an immutable function called that extracts all user ids from json
> as an array:
> `create function extractUserIds(p_elements json) returns array as $$
> select array(select ... from json_array_elements(p_elements->...)); $$ ...;`
> 2) create a functional gin or gist index : `create index ... on ... using
> ... (extractUserIds(elements));`
> 3) use conditions like `where extractUserIds(elements) && array[...]`
>
> Alternatively, I'd consider a schema redesign, as it looks like you may
> benefit from a normalized schema.
>
> Best,
> Alex
>
--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to create index on json array in postgres
2019-01-01 05:49 How to create index on json array in postgres Surya S <surya.s@citrusinformatics.com>
2019-01-04 17:10 ` Re: How to create index on json array in postgres Alexey Bashtanov <bashtanov@imap.cc>
2019-01-04 20:52 ` Re: How to create index on json array in postgres Rene Romero Benavides <rene.romero.b@gmail.com>
@ 2019-01-04 21:04 ` Steve Midgley <science@misuse.org>
0 siblings, 0 replies; 4+ messages in thread
From: Steve Midgley @ 2019-01-04 21:04 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
On Fri, Jan 4, 2019 at 12:52 PM Rene Romero Benavides <
rene.romero.b@gmail.com> wrote:
> What made you guys use a json array schema in the first place? is there a
> requirement for storing highly variable fields or introducing/removing new
> fields "on the fly"? IMHO, it's better a normalized approach if fields
> don't vary that much for each record and you need to perform ad-hoc queries
> in a performant manner, it would also make partitioning easier to implement
> if needed.
> How many records do you expect to have in this table? how do you expect it
> to grow? what's the maximum array elements that each record could possibly
> have? how are they going to be updated? frequently? rarely? are you
> considering partitioning for this table?
> If you really need the flexibility of the json data type, what about
> creating independent fields for the common filters? you would be
> duplicating information, but your indexes and queries would be less
> complex.
>
> On Fri, Jan 4, 2019 at 11:10 AM Alexey Bashtanov <bashtanov@imap.cc>
> wrote:
>
>>
>> I have a json field called 'elements' in my table demo which
>> contains an array 'data' containing key value pairs. the 'data' array has
>> the below structure. the data array may have multiple json entries.I am
>> using postgres version 9.5
>>
>> { "data": [{ "ownr": "1", "siUsr": [2], "sigStat": "APPR", "modifiedOn":
>> 1494229698039, "isDel": "false", "parentId": "nil", "disName": "exmp.json",
>> "uniqueId": "d88cb52", "usrType": "owner", "usrId": "1", "createdOn":
>> 1494229698039, "obType": "file" }] }
>>
>> In my query I have multiple filters based on obj(Eg : obj->>usrId,
>> obj->>siUsr etc) where obj corresponds to
>> json_array_elements(demo.elements->'data').How do I create btree indices on
>> filters like obj->>userId ,obj->>sigUsr? Please revert.
>>
>>
>> I would maybe
>> 1) make an immutable function called that extracts all user ids from json
>> as an array:
>> `create function extractUserIds(p_elements json) returns array as $$
>> select array(select ... from json_array_elements(p_elements->...)); $$ ...;`
>> 2) create a functional gin or gist index : `create index ... on ... using
>> ... (extractUserIds(elements));`
>> 3) use conditions like `where extractUserIds(elements) && array[...]`
>>
>> Alternatively, I'd consider a schema redesign, as it looks like you may
>> benefit from a normalized schema.
>>
>> Best,
>> Alex
>>
>
>
> --
> El genio es 1% inspiración y 99% transpiración.
> Thomas Alva Edison
> http://pglearn.blogspot.mx/
>
> [snippet]
> What made you guys use a json array schema in the first place? is there a
> requirement for storing highly variable fields or introducing/removing new
> fields "on the fly"? IMHO, it's better a normalized approach if fields
> don't vary that much for each record and you need to perform ad-hoc queries
> in a performant manner, it would also make partitioning easier to implement
> if needed.
+1 Based on the work we've been doing with "document style" records in
Postgres over the last few years, I would also highly recommend a static
(or at least versioned) approach to your json structure/schema. We use
json-schema.org language to define and validate our schemas so we know
what's going into Pg is what's expected. I hope this input is helpful!
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-01-04 21:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-01 05:49 How to create index on json array in postgres Surya S <surya.s@citrusinformatics.com>
2019-01-04 17:10 ` Alexey Bashtanov <bashtanov@imap.cc>
2019-01-04 20:52 ` Rene Romero Benavides <rene.romero.b@gmail.com>
2019-01-04 21:04 ` Steve Midgley <science@misuse.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox