Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sPXV6-005744-L3 for pgsql-general@arkaria.postgresql.org; Fri, 05 Jul 2024 01:12:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sPXV2-004cGp-Mz for pgsql-general@arkaria.postgresql.org; Fri, 05 Jul 2024 01:12:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sPXV2-004cGg-Bq for pgsql-general@lists.postgresql.org; Fri, 05 Jul 2024 01:12:33 +0000 Received: from mail-oo1-xc2f.google.com ([2607:f8b0:4864:20::c2f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sPXUw-000SEB-8L for pgsql-general@postgresql.org; Fri, 05 Jul 2024 01:12:31 +0000 Received: by mail-oo1-xc2f.google.com with SMTP id 006d021491bc7-5c477d97159so530432eaf.3 for ; Thu, 04 Jul 2024 18:12:26 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1720141945; x=1720746745; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=cQOXGIpdYW0ZqiW5jHqIQCUGEFa7k7Ij6n66tE+u5bA=; b=Sel4EqUMcieKaTRjiKeNN7inyKaye4q2Cf2eFXk5mgLtJDlfE2w8c8sR+lbUmoO3pZ zRAINC3FRQ0oY2Yyl+DUx3jXyCPTgwQkGAm7PL4HxTdbldDhIBBmV4k0/KW+3iEKX/uZ 1V/T9Jm7Dhs5PyW74+YykKrEsTrr2dBT5N/tApJiNpo+RlE2DBYm/22pis9XGM6Q/Q6U 0FKKKlUWQXsMlT0WB6KQx2zF6MKunQ8LmpIEogrsxTDXhd4XnWj8YvDE6iEe8sTBqBod WXji+LMRI7ESoWlwESw5XyRAyrykM/fvLpaehrWmFkPzzlhkg3HTkkknsJUMVxV4ZFk6 IZQQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1720141945; x=1720746745; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=cQOXGIpdYW0ZqiW5jHqIQCUGEFa7k7Ij6n66tE+u5bA=; b=TMi2MC0hUvFqyYaaGEJ2ECvwVncfGoBQKPXYwt8QyCGv2DOOylTuVBkJsXCDE6s/Cs W9pkdITv5rvs4v2Ms6WydVBgRqpbmScmehWxxrIxnhF/swdcagR+qAKW4YxQCD3Q0s0l y1VcmVnq8VejoeSGiCLevJlyQrOPQHURChTECHRpPoLjYnjZhxtjgbQGI93N3Tc/AKqh sHrpO0MIc4TVrpzwWUDvTRfGIeqVqAZULmzAYAoFJ1SiH3ZEeZk/S30AMpPO7Tf0oRjx CYyQ0BQ+4uku2alikA/u9D61V3h7+noRc/eDL7yIzvkGi0ijN95iwDCTIzCDf2dfeKOX ThEQ== X-Gm-Message-State: AOJu0YxQJX7ZMLS/L/yRvmsm06QdO10aSExSqnsKJqGju3kKqAXECGSv rjU5VcK+r5ffS803uss4HUnQO+ADJ7HZpabg4qOjA0AWleJsvpnDeuM7HN2A33uEvndmQddGFfF eIhLD5TXVSIJIxKHRrd5YGCaJ5aAz3xR2 X-Google-Smtp-Source: AGHT+IGA5kUXOiku4jEzS9QQr3n31SO8rOnNQa+yXveefS768FTG4jWOLvI5f3xSs0jXeqKH6clTsNGkPvQc7D20tV0= X-Received: by 2002:a4a:106:0:b0:5c4:2497:c92d with SMTP id 006d021491bc7-5c646e2cd2amr3927674eaf.2.1720141945505; Thu, 04 Jul 2024 18:12:25 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: "David G. Johnston" Date: Thu, 4 Jul 2024 18:11:47 -0700 Message-ID: Subject: Re: JSONPath operator and escaping values in query To: Vasu Nagendra Cc: "pgsql-general@postgresql.org" Content-Type: multipart/alternative; boundary="000000000000a00ec0061c75c173" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a00ec0061c75c173 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Jul 4, 2024 at 1:57=E2=80=AFPM Vasu Nagendra = wrote: > > > SELECT '{"n": {"a-b": 1, "@ab": 2, "ab": 3}}'::jsonb @? '$ ? (@.n.a\-b >= =3D 3)'; > > Which is better written as: select '{"n": {"a-b": 1, "@ab": 2, "ab": 3}}'::jsonb @? '$ ? (@.n."a-b" >= =3D 3)'; Using the same double-quotes you defined the key with originally. The relevant documentation for jsonpath syntax is here: https://www.postgresql.org/docs/current/datatype-json.html#DATATYPE-JSONPAT= H David J. > --000000000000a00ec0061c75c173 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Jul 4, 2024 at 1:57=E2=80=AFPM Vasu Nagendra <<= a href=3D"mailto:vasu@termtegrity.com">vasu@termtegrity.com> wrote:<= /span>

=C2=A0

SELECT '{"n": {&quo=
t;a-b": 1, "@ab": 2, "ab": 3}}'<=
span style=3D"font-size:13.5pt;font-family:"Cascadia Mono";color:=
rgb(8,8,8)">::jsonb @? '$ ? (@.n.=
a\-b >=3D 3)';

Which is better written as:

<= div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif= ">select '{"n": {"a-b": 1, "@ab": 2, &quo= t;ab": 3}}'::jsonb @? '$ ? (@.n."a-b" >=3D 3)'= ;;

Using the same double-quotes you defined the ke= y with originally.

The relevant documentation for json= path syntax is here:

https://www.postg= resql.org/docs/current/datatype-json.html#DATATYPE-JSONPATH

David J.
--000000000000a00ec0061c75c173--