Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gfSzj-0006GF-RF for pgsql-sql@arkaria.postgresql.org; Fri, 04 Jan 2019 17:10:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gfSzh-0000HT-Fl for pgsql-sql@arkaria.postgresql.org; Fri, 04 Jan 2019 17:10:49 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gfSzf-00005f-Br for pgsql-sql@lists.postgresql.org; Fri, 04 Jan 2019 17:10:49 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gfSzb-0005wj-Up for pgsql-sql@lists.postgresql.org; Fri, 04 Jan 2019 17:10:46 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.42]) by mailout.nyi.internal (Postfix) with ESMTP id D794C2528C; Fri, 4 Jan 2019 12:10:42 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute2.internal (MEProxy); Fri, 04 Jan 2019 12:10:42 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=imap.cc; h= subject:to:references:from:message-id:date:mime-version :in-reply-to:content-type; s=fm2; bh=rx7/JSWcr2qoJVTYmALwLYvJdNZ IgdaOQljOs3ras2U=; b=Dpxug49pt1EH7Bg7p3/RZT7vvZyAC/A0dvvnmY+VhTf Dcxu8O+p0Ikqq7peBm3/jXLczw/d+HncQUSkAz2k8M6QkzGiVWh29rhoGwXQg+DR 88Resw3lxhJZC6j/dqHm3ErpRjR2tmhgjRU99scVYahFnwg3g4rtA/Sy2XT3jTNt w5kHq0SS0gz9JhNoHCGuP+I9LGNkv1esPVtAQqhfoCfC72r7u2tSTcshvuSnjvYr WF+Dt5ojfVtR7du2s9Ss+vuWqgQhuupiUaT8JdlhRHuibSAmA8Lk9QC8FWEeq5hA uKkp+9A7exZblmQ7GmsJibTE921xLqWJ0U3lsESxw+Q== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; bh=rx7/JS Wcr2qoJVTYmALwLYvJdNZIgdaOQljOs3ras2U=; b=lFiKiNRK2nDKbHsJMj+A1v 45WneIINc9EM54Vi/g8fPjXzh+vfZ5FsXnK8HHYwRHBMCDcJW1QktC1cDqZo55GZ qwohwt6t963Y8NiIraJScK9I1eKTkRgLttgblBsuaSs9Xj01mZfVsNbchvAisPnp 3zVcicAEja0qk4m6IkVxvk6xLjdK9PyARns81+HdMzn8SQt+BhSHcRQMIeKpWhfS D1murdBIe+LRQV3Ehc/gUiJ/fSCT8gfZQ+6NxYrVpW/3kjcuV+88E5zuYVGPJg+M TRyrW/thcZ55bguk5LsZUdL+4SoaVxm+0RgS8jiyzjt2ukPYlJyLi9E+8lR8qwBA == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedtledrvddugddutddtucdltddurdegtdekrddttd dmucetufdoteggodetrfdotffvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfquhht necuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmd enfghrlhcuvffnffculdeftddmnecujfgurhepuffvfhfhkffffgggjggtsegrtderredt feejnecuhfhrohhmpeetlhgvgigvhicuuegrshhhthgrnhhovhcuoegsrghshhhtrghnoh hvsehimhgrphdrtggtqeenucfkphepudekhedrvdehrdeffedrjedtnecurfgrrhgrmhep mhgrihhlfhhrohhmpegsrghshhhtrghnohhvsehimhgrphdrtggtnecuvehluhhsthgvrh fuihiivgeptd X-ME-Proxy: Received: from [10.2.33.124] (unknown [185.25.33.70]) by mail.messagingengine.com (Postfix) with ESMTPA id 13F2A10107; Fri, 4 Jan 2019 12:10:40 -0500 (EST) Subject: Re: How to create index on json array in postgres To: Surya S , pgsql-sql@lists.postgresql.org References: <08b38bb7-c0a5-1fe1-3e13-536383d66a2e@citrusinformatics.com> From: Alexey Bashtanov Message-ID: <608c5341-2ef1-57c1-9db1-5c0b45b2de59@imap.cc> Date: Fri, 4 Jan 2019 17:10:37 +0000 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.2.1 MIME-Version: 1.0 In-Reply-To: <08b38bb7-c0a5-1fe1-3e13-536383d66a2e@citrusinformatics.com> Content-Type: multipart/alternative; boundary="------------3C037E290641E584D7879512" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------3C037E290641E584D7879512 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit >      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 --------------3C037E290641E584D7879512 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

     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
--------------3C037E290641E584D7879512--