Re: How to create index on json array in postgres

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/