Re: How to create index on json array in postgres

On Fri, Jan 4, 2019 at 12:52 PM Rene Romero Benavides <
rene(dot)romero(dot)b(at)gmail(dot)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(at)imap(dot)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!