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