Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qA9Rn-0006EX-20 for pgsql-general@arkaria.postgresql.org; Fri, 16 Jun 2023 13:25:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qA9Rm-0003Y1-0z for pgsql-general@arkaria.postgresql.org; Fri, 16 Jun 2023 13:25:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qA9Rl-0003Xe-Li for pgsql-general@lists.postgresql.org; Fri, 16 Jun 2023 13:25:01 +0000 Received: from mout.gmx.net ([212.227.17.21]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qA9Rg-002nDA-Tz for pgsql-general@lists.postgresql.org; Fri, 16 Jun 2023 13:25:01 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1686921895; x=1687526695; i=shammat@gmx.net; bh=wCIgo+R95x1KbmzbePXVvlT0X102GxPbvsgxX8QF7sM=; h=X-UI-Sender-Class:Subject:To:References:From:Date:In-Reply-To; b=g5KEzGgSjOrFM/QuKn6nM1jzg78RELDbZ6E6bBHQAK/DqErgFeEiQzKXSx32r+Ljz3Mwsip A8gM059GGLuOzq/d1ReeLEp/hDtZswQZcm2TPTdcP587IWBQ0ZSq+A0n/PryYqqo0jbiw7Dd/ hlFQ08qJ/tGbjuKXtDWrHIIW5yq8AryyRYxS+MDiHSX8YgRKZ//I45c2swbBbsbAFcQAgYTfb /iYo/uBDaHRqYHlxIkIUnAZryjMtgjM5pOnH0mgOMgpNzYrmkhcvxhwYY25LOJLka+HJnSQ9t wUxD6WgoEqf42mTvqBBNFb8xtK2VUiUqTr8wIgmqk6f+rbMPHUAA== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.1.32.103] ([185.40.248.10]) by mail.gmx.net (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1MNKlu-1qUSI1229q-00OpDZ for ; Fri, 16 Jun 2023 15:24:55 +0200 Subject: Re: Using vars in jsonbpath operator ? To: pgsql-general@lists.postgresql.org References: <4A219290-0DC1-4782-85F6-55CA334192C3@gmail.com> From: Thomas Kellerer Message-ID: <39a04bd8-aefd-b02e-594b-ca875a884048@gmx.net> Date: Fri, 16 Jun 2023 15:24:54 +0200 User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23) Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666 MIME-Version: 1.0 In-Reply-To: <4A219290-0DC1-4782-85F6-55CA334192C3@gmail.com> Content-Type: text/plain; charset=utf-8 Content-Language: de-DE Content-Transfer-Encoding: quoted-printable X-Provags-ID: V03:K1:u6zQcW0bI4NpL0NjE/bmNWkQiDBb4J9iFkJ72rSc8k5wcHU2AUz elToUvEnbTqRIK7Efj2rXrJhRfK5FHv0hrtknKBGl6slZ4CCRaFPF1ask1EbL/NuKOS+QCn N1LWQRtgqHe4Vu+rhZG0FV9dtqWbqRl1oTSuOCYJyUTN2h59KhEq/MffUA95UVXL2E4ai8p nZ/9d27uYE4hAvFE+ZsfQ== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:erzRAqocq+0=;0Sr/bJ66kqfJrluE899dYTeDlWz 078N12F9sWoWDUeMSlskYW91B73wQcp2YZVYKebtlCFHFSHhgaul1S9Psoikc3bVNIsKqMD19 HyFtB71LCxVpUfBq222+e1mLCGVf6+aspAuhLvXhq87TMkx2meeQH2wQ12trgkIXne3DSy/M7 ZnXPlJTVF/1gS11fzU/oRfxzvfxaVyk4v1tyl0GDek/osUh5S1ismQFMdHmlhzBkub9V0sVfs LyryNqHPLrmOQ8PhNaAtzOgqke1dsbMMx6Nmvkxe+kXIljUBjjw7PvfzEpAak3iUM2sfIzCw9 6c4Ge0K/TcWwmeBLuE04oDVKAqvkMw7UNXlWZBuIeuXDQ69QtbXvcHyYx21qCS5WqaOuLTU1Z a+v0JQWBeQNs5+oDmvvDjBQPNKzJyRID7NWc38xEnB2ZeVag6DK1ZNT9CQqYABvrVOslGD0wn Mu1sElMyXCcXHLt/aiPXxRcdQGNxaveYZhElpznTYysUo8x3ZdLJXukSKReVlt7+t58VxCqrV 8ofHdzwQdv8wdcy4K13kdeUAkhiHvlxaoSmdfLdUeoBGeaxrd8oLeXogsIK4yOwRmMbajmkCV kNq8lekRADpaJhUe0hlShJVznIqGCT1WMj3uaY8RkcUpnCJM4zKxu+063Qa2JqUk1T/YkRxnT 9cdcFfEwsU7X5LCxMwpsLtICtWFo4Yulb5Tn8nZe//0KRM9stcXG2cJBLg4v9FgTAGjiTk3sY c4F6KAG+OAJFksyag99hIOJlB2D5QpN4WKuYFlO8CYCvEbQrC4/Tw5rDf3UXOrlAvOw4aSmxs UZLk5HfgZlmBC6z0HXZ1M5IDDsElkz1MAbC8E3y1XF+NKiFDRyptQkyf7P/xbLl4RGuxX7lR+ XVAGR8dCVDYad2pNrtAENKmLi/HpiK6NMswxtW8hiOEN/88eHg7eYm1dWOYpI6nNSaO1mOeGF fBX9U8VOFBRvJ9qvPede0SiZaoc= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Markur Sens schrieb am 16.06.2023 um 13:54: > I understand that on a where clause a gin index can be used for the foll= owing predicate > > a.data @? '$.results.docs[*].accs[*] ? (@.id =3D=3D =E2=80=9C123") > > I have a join query however on the following condition > > jsonb_path_exists(a.data, '$.results.docs[*].accs[*] ? (@.number =3D=3D = $id)', jsonb_build_object(=E2=80=98id', *b.id*)) =C2=A0but this cannot use= a gin index afaik and results in a seq scan. > > Is there anyway to use the operator syntax above by passing the b.id =C2=A0variable somwehow so that the gin index can be used ? ?=C2= =A0 > I think you will need to pass the whole jsonpath as a parameter, e.g. a.data @? cast($1 as jsonpath) At least that's the only way I could achieve this