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 1geCwb-00034C-5d for pgsql-sql@arkaria.postgresql.org; Tue, 01 Jan 2019 05:50:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1geCwZ-0007BS-6H for pgsql-sql@arkaria.postgresql.org; Tue, 01 Jan 2019 05:50:23 +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 1geCwY-00078P-Bi for pgsql-sql@lists.postgresql.org; Tue, 01 Jan 2019 05:50:23 +0000 Received: from biz146.inmotionhosting.com ([23.235.213.242]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1geCwL-0003jx-16 for pgsql-sql@lists.postgresql.org; Tue, 01 Jan 2019 05:50:21 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=citrusinformatics.com; s=default; h=Content-Type:MIME-Version:Date: Message-ID:Subject:From:To:Sender:Reply-To:Cc:Content-Transfer-Encoding: Content-ID:Content-Description:Resent-Date:Resent-From:Resent-Sender: Resent-To:Resent-Cc:Resent-Message-ID:In-Reply-To:References:List-Id: List-Help:List-Unsubscribe:List-Subscribe:List-Post:List-Owner:List-Archive; bh=+/zW+NkJSQzu6HgmDeiv9/mY/bhNH56rbat9A+DRTzM=; b=QXW2/UrYMLf53eFMkvsh2d03I cj/BaE9sB9QZPSpgCkaYPMvluiTZSgwntdOhPaABtBgJZAgUMH5hiSbgQZX6PVYyK2wNaV1rHk5/N PCwe1LjK+jkVpm9WN2IfSdwxElzzT+EfXacNzmPn6DAXGJ5sUMDUq0ZdtMf+fjk2eeLNUQy+YcMkW /Ma1+4RpIumAhHaP0CBVPyMKOnSAGEYsP4R4pHhdT8X/wp+XQld2U1VF4koNBGLH//pUM7BgfT2Nm /2CTIzRrMzKmcnAxF4us2fPC3Gnorms2IqLu9Pz8HCNyyj6+gJTN7pbWe/Nth762B6sBdwAtggTKR tpxdP6/4Q==; Received: from [202.83.55.151] (port=49168 helo=[127.0.0.1]) by biz146.inmotionhosting.com with esmtpsa (TLSv1.2:ECDHE-RSA-AES128-GCM-SHA256:128) (Exim 4.91) (envelope-from ) id 1geCwD-007b9M-UG for pgsql-sql@lists.postgresql.org; Mon, 31 Dec 2018 21:50:07 -0800 To: pgsql-sql@lists.postgresql.org From: Surya S Subject: How to create index on json array in postgres Message-ID: <08b38bb7-c0a5-1fe1-3e13-536383d66a2e@citrusinformatics.com> Date: Tue, 1 Jan 2019 11:19:59 +0530 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:60.0) Gecko/20100101 Thunderbird/60.3.3 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="------------1B1F078A4D7E765A5C5E7290" Content-Language: en-US X-OutGoing-Spam-Status: No, score=-101.0 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - biz146.inmotionhosting.com X-AntiAbuse: Original Domain - lists.postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - citrusinformatics.com X-Get-Message-Sender-Via: biz146.inmotionhosting.com: authenticated_id: surya.s@citrusinformatics.com X-Authenticated-Sender: biz146.inmotionhosting.com: surya.s@citrusinformatics.com X-Source: X-Source-Args: X-Source-Dir: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------1B1F078A4D7E765A5C5E7290 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------1B1F078A4D7E765A5C5E7290 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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

--------------1B1F078A4D7E765A5C5E7290--