How to create index on json array in postgres

agora inbox for pgsql-sql@postgresql.org  
help / color / mirror / Atom feed

From: Surya S <surya.s@citrusinformatics.com>
To: pgsql-sql@lists.postgresql.org
Subject: How to create index on json array in postgres
Date: Tue, 1 Jan 2019 11:19:59 +0530
Message-ID: <08b38bb7-c0a5-1fe1-3e13-536383d66a2e@citrusinformatics.com> (raw)

Hi,

      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.

Regards

Surya

view thread (4+ messages)  latest in thread

Message-ID: <08b38bb7-c0a5-1fe1-3e13-536383d66a2e@citrusinformatics.com>
Permalink:  ../08b38bb7-c0a5-1fe1-3e13-536383d66a2e@citrusinformatics.com/
Also on:    postgresql.org/message-id/08b38bb7-c0a5-1fe1-3e13-536383d66a2e@citrusinformatics.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: surya.s@citrusinformatics.com, pgsql-sql@lists.postgresql.org
  Subject: Re: How to create index on json array in postgres
  In-Reply-To: <08b38bb7-c0a5-1fe1-3e13-536383d66a2e@citrusinformatics.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox