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