Re: How to create index on json array in postgres

>      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