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 1qA82R-0002sT-4B for pgsql-general@arkaria.postgresql.org; Fri, 16 Jun 2023 11:54:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qA82Q-0000aZ-31 for pgsql-general@arkaria.postgresql.org; Fri, 16 Jun 2023 11:54:46 +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 1qA82P-0000Z2-MU for pgsql-general@lists.postgresql.org; Fri, 16 Jun 2023 11:54:45 +0000 Received: from mail-ed1-x536.google.com ([2a00:1450:4864:20::536]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qA82M-002lwM-L6 for pgsql-general@lists.postgresql.org; Fri, 16 Jun 2023 11:54:45 +0000 Received: by mail-ed1-x536.google.com with SMTP id 4fb4d7f45d1cf-51878f8e541so746633a12.3 for ; Fri, 16 Jun 2023 04:54:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1686916481; x=1689508481; h=to:date:message-id:subject:mime-version:from:from:to:cc:subject :date:message-id:reply-to; bh=mvPyyreBbqlkRm1qSpxBXrowlmdBq5WDusMp/0S1lDg=; b=Qif6/RzyU64L6m212/vXeVo+vU3hF3NUhI06rqbrXziDA6eaPcYgP9/vpwtqqbieyf UBTU/CjbBi3xIqxbYYRjnBJW2ruFkT9SxKBYE6mCel5AFBqVbCYJp3srC5j6RYuvXfiW fyznATU8Gf83mS5xAOVtoILskt9VLM3cHQRx5o309UpM7bS4Yq9sY3UNwkWNDgX0MPeD ub2iXnbHdf5Zkjt879MFpT5kMNz8wrysOrovt1rBvSl4ezk5P0Q7Sk7RvEVyXYbdZcNw GS0cZOKoXmvVSxGOA0yoFrYN4pNSxJcShRoPIiGqQFHpbRWGeU8dbky+u6BgMHTHWjy5 h6vA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1686916481; x=1689508481; h=to:date:message-id:subject:mime-version:from:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=mvPyyreBbqlkRm1qSpxBXrowlmdBq5WDusMp/0S1lDg=; b=lQ3YR9qgY3TrYR9ekA/v7FcZxtiqQ4SVJ1UkZFJ9gcHtJDOfGYKYeNRR59cj+gYVjR k/KTwkekeHi/g7n1bf+Finxq3t6BkY0pj7G4R9DH4RIpYP4OUXp07eCghgRT3UMOT53D jwBzuCdMuNl/NB1zM2sNF+OrKfBBMWxrWFVUUfSoUxl4DrFePb6CKNbT6w3LunTZ8jYn FHiGPzb61ZMEujzXNVdzv1pbPxWhJBUqLEteODSjuWyE9hr1xXtubxVbWFvEEqqznYhK 0uPMnVC+b2a8zYpUJO2moOm0qxcreA+Trthm2wC1479aTnwjrvCrDrsK/bB7BxK6/kuV ueVw== X-Gm-Message-State: AC+VfDx5TT5HlblkGlywj6lYJkWAChJPbfcVDZc+pKbhIwdDuY/+KgLb q+lXEhsVWYuSVFyh7mkAYa96M2+1B4g= X-Google-Smtp-Source: ACHHUZ5FuEN0jGDTVINLHczRvf4VJtLmI/U+BhTgKy9edxPsZdwak/nc6xJO7kqLG2hYRD/sFSwhWQ== X-Received: by 2002:a17:907:16a2:b0:977:4b68:bf2 with SMTP id hc34-20020a17090716a200b009774b680bf2mr1734536ejc.54.1686916480859; Fri, 16 Jun 2023 04:54:40 -0700 (PDT) Received: from smtpclient.apple (adsl-63.176.58.139.tellas.gr. [176.58.139.63]) by smtp.gmail.com with ESMTPSA id a9-20020a17090682c900b009828ee60764sm2615611ejy.42.2023.06.16.04.54.40 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 16 Jun 2023 04:54:40 -0700 (PDT) From: Markur Sens Content-Type: multipart/alternative; boundary="Apple-Mail=_396425C0-008E-46BE-8809-32CBBF442658" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.600.7\)) Subject: Using vars in jsonbpath operator ? Message-Id: <4A219290-0DC1-4782-85F6-55CA334192C3@gmail.com> Date: Fri, 16 Jun 2023 14:54:35 +0300 To: pgsql-general X-Mailer: Apple Mail (2.3731.600.7) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_396425C0-008E-46BE-8809-32CBBF442658 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 I understand that on a where clause a gin index can be used for the = following 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)) but 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 = variable somwehow so that the gin index can be used ? ?=20= --Apple-Mail=_396425C0-008E-46BE-8809-32CBBF442658 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
I = understand that on a where clause a gin index can be used for the = following 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))  but 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 variable somwehow so that the gin = index can be used ? = ? 




<= /div>= --Apple-Mail=_396425C0-008E-46BE-8809-32CBBF442658--