agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Alexey Bashtanov <bashtanov@imap.cc>
To: Surya S <surya.s@citrusinformatics.com>
To: pgsql-sql@lists.postgresql.org
Subject: Re: How to create index on json array in postgres
Date: Fri, 4 Jan 2019 17:10:37 +0000
Message-ID: <608c5341-2ef1-57c1-9db1-5c0b45b2de59@imap.cc> (raw)
In-Reply-To: <08b38bb7-c0a5-1fe1-3e13-536383d66a2e@citrusinformatics.com>
References: <08b38bb7-c0a5-1fe1-3e13-536383d66a2e@citrusinformatics.com>
> 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
view thread (4+ messages) latest in thread
Message-ID: <608c5341-2ef1-57c1-9db1-5c0b45b2de59@imap.cc>
Permalink: ../608c5341-2ef1-57c1-9db1-5c0b45b2de59@imap.cc/
Also on: postgresql.org/message-id/608c5341-2ef1-57c1-9db1-5c0b45b2de59@imap.cc
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: bashtanov@imap.cc, surya.s@citrusinformatics.com, pgsql-sql@lists.postgresql.org
Subject: Re: How to create index on json array in postgres
In-Reply-To: <608c5341-2ef1-57c1-9db1-5c0b45b2de59@imap.cc>
* 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