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 1qrlR1-005jzw-CO for pgsql-hackers@arkaria.postgresql.org; Sat, 14 Oct 2023 20:40:31 +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 1qrlQx-008Ye8-Ac for pgsql-hackers@arkaria.postgresql.org; Sat, 14 Oct 2023 20:40:28 +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 1qrlQw-008Ydv-I6 for pgsql-hackers@lists.postgresql.org; Sat, 14 Oct 2023 20:40:27 +0000 Received: from pb-smtp1.pobox.com ([64.147.108.70]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qrlQo-000UlM-Pl for pgsql-hackers@lists.postgresql.org; Sat, 14 Oct 2023 20:40:24 +0000 Received: from pb-smtp1.pobox.com (unknown [127.0.0.1]) by pb-smtp1.pobox.com (Postfix) with ESMTP id 76DCA1B344A for ; Sat, 14 Oct 2023 16:40:16 -0400 (EDT) (envelope-from david@justatheory.com) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed; d=pobox.com; h=from :content-type:mime-version:subject:message-id:date:to; s=sasl; bh=6Iphx/CDY9gtFWEXwb5UAqnNSQej+NJp8eybi7ifaKE=; b=qdvBZchpA1Ce i4C/+ZQBng2rEPEWFABdCcpeEv9IhmCQKJC4qJChNM+K107F6fgfrHKK56g6ikl9 a1kfdTA2ijzkkArgfbr67MdBdj+ahT83MvlWVl4kZzz5RapRaJXGMyQ4hY9zhhjv IVh46HXkX1Hikyp882QmNc60f3Q5Nnk= Received: from pb-smtp1.nyi.icgroup.com (unknown [127.0.0.1]) by pb-smtp1.pobox.com (Postfix) with ESMTP id 6E2E21B3449 for ; Sat, 14 Oct 2023 16:40:16 -0400 (EDT) (envelope-from david@justatheory.com) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed; d=justatheory.com; h=from:content-type:mime-version:subject:message-id:date:to; s=2016-12.pbsmtp; bh=6Iphx/CDY9gtFWEXwb5UAqnNSQej+NJp8eybi7ifaKE=; b=Gqrcjf85VSMIPcDMszOX4vP7WbO/t6d038L/MxvcLaZS/DGHaaLrrOGBlZE0rEghqDWi8U0eR8lYdrQHD60hmUWP9rpiX+vmzo92q+vem/iZJmtoLUjJSqGeYbfgVuwFheVFLlaZbvjeeTaPROaAnpBqeNl7S5T8u08ZuXIBA5g= Received: from smtpclient.apple (unknown [158.222.197.125]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by pb-smtp1.pobox.com (Postfix) with ESMTPSA id DB7F11B3445 for ; Sat, 14 Oct 2023 16:40:15 -0400 (EDT) (envelope-from david@justatheory.com) From: "David E. Wheeler" Content-Type: multipart/mixed; boundary="Apple-Mail=_C167A746-C142-4F05-B098-2EBC6EE99865" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.100.2.1.4\)) Subject: Patch: Improve Boolean Predicate JSON Path Docs Message-Id: <7262A188-59CA-4A8A-AAD7-83D4FF0B9758@justatheory.com> Date: Sat, 14 Oct 2023 16:40:05 -0400 To: pgsql-hackers@lists.postgresql.org X-Mailer: Apple Mail (2.3774.100.2.1.4) X-Pobox-Relay-ID: E1843F56-6AD1-11EE-937A-78DCEB2EC81B-76319746!pb-smtp1.pobox.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_C167A746-C142-4F05-B098-2EBC6EE99865 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hackers, Following up from a suggestion from Tom Lane[1] to improve the = documentation of boolean predicate JSON path expressions, please find = enclosed a draft patch to do so. It does three things: 1. Converts all of the example path queries to use jsonb_path_query() = and show the results, to make it clearer what the behaviors are. 2. Replaces the list of deviations from the standards with a new = subsection, with each deviation in its own sub-subsection. The regex = section is unchanged, but I=E2=80=99ve greatly expanded the boolean = expression JSON path section with examples comparing standard filter = expressions and nonstandard boolean predicates. I=E2=80=99ve also added = an exhortation not use boolean expressions with @? or standard path = expressions with @@. 3. While converting the modes section to use jsonb_path_query() and show = the results, I also added an example of strict mode returning an error. Follow-ups I=E2=80=99d like to make: 1. Expand the modes section to show how the types of results can vary = depending on the mode, thanks to the flattening. Examples: david=3D# select jsonb_path_query('{"a":[1,2,3,4,5]}', '$.a ?(@[*] > = 2)'); jsonb_path_query=20 ------------------ 3 4 5 (3 rows) david=3D# select jsonb_path_query('{"a":[1,2,3,4,5]}', 'strict $.a = ?(@[*] > 2)'); jsonb_path_query =20 ------------------ [1, 2, 3, 4, 5] 2. Improve the descriptions and examples for @?/jsonb_path_exists() and = @@/jsonb_path_match(). Best, David [1] = https://www.postgresql.org/message-id/1229727.1680535592%40sss.pgh.pa.us --Apple-Mail=_C167A746-C142-4F05-B098-2EBC6EE99865 Content-Disposition: attachment; filename=jsonpath-pred-docs.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="jsonpath-pred-docs.patch" Content-Transfer-Encoding: 7bit diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index affd1254bb..295f8ca5c9 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -17205,7 +17205,7 @@ array w/o UK? | t For example, suppose you have some JSON data from a GPS tracker that you would like to parse, such as: -{ + \set json '{ "track": { "segments": [ { @@ -17220,7 +17220,7 @@ array w/o UK? | t } ] } -} +}' @@ -17229,7 +17229,10 @@ array w/o UK? | t .key accessor operator to descend through surrounding JSON objects: -$.track.segments +select jsonb_path_query(:'json'::jsonb, '$.track.segments'); + jsonb_path_query +------------------------------------------------------------------------------------------------------------------------------------------------------------------- + [{"HR": 73, "location": [47.763, 13.4034], "start time": "2018-10-14 10:05:14"}, {"HR": 135, "location": [47.706, 13.2635], "start time": "2018-10-14 10:39:21"}] @@ -17239,7 +17242,11 @@ $.track.segments the following path will return the location coordinates for all the available track segments: -$.track.segments[*].location +select jsonb_path_query(:'json'::jsonb, '$.track.segments[*].location'); + jsonb_path_query +------------------- + [47.763, 13.4034] + [47.706, 13.2635] @@ -17248,7 +17255,10 @@ $.track.segments[*].location specify the corresponding subscript in the [] accessor operator. Recall that JSON array indexes are 0-relative: -$.track.segments[0].location +select jsonb_path_query(:'json'::jsonb, 'strict $.track.segments[0].location'); + jsonb_path_query +------------------- + [47.763, 13.4034] @@ -17259,7 +17269,10 @@ $.track.segments[0].location Each method name must be preceded by a dot. For example, you can get the size of an array: -$.track.segments.size() +select jsonb_path_query(:'json'::jsonb, 'strict $.track.segments.size()'); + jsonb_path_query +------------------ + 2 More examples of using jsonpath operators and methods within path expressions appear below in @@ -17302,7 +17315,10 @@ $.track.segments.size() For example, suppose you would like to retrieve all heart rate values higher than 130. You can achieve this using the following expression: -$.track.segments[*].HR ? (@ > 130) +select jsonb_path_query(:'json'::jsonb, '$.track.segments[*].HR ? (@ > 130)'); + jsonb_path_query +------------------ + 135 @@ -17312,7 +17328,10 @@ $.track.segments[*].HR ? (@ > 130) filter expression is applied to the previous step, and the path used in the condition is different: -$.track.segments[*] ? (@.HR > 130)."start time" + select jsonb_path_query(:'json'::jsonb, '$.track.segments[*] ? (@.HR > 130)."start time"'); + jsonb_path_query +----------------------- + "2018-10-14 10:39:21" @@ -17321,7 +17340,10 @@ $.track.segments[*] ? (@.HR > 130)."start time" example, the following expression selects start times of all segments that contain locations with relevant coordinates and high heart rate values: -$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time" +select jsonb_path_query(:'json'::jsonb, '$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"'); + jsonb_path_query +----------------------- + "2018-10-14 10:39:21" @@ -17330,46 +17352,81 @@ $.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time" The following example first filters all segments by location, and then returns high heart rate values for these segments, if available: -$.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130) +select jsonb_path_query(:'json'::jsonb, $.track.segments[*] ? (@.location[1] < 13.4).HR ? (@ > 130)'); + jsonb_path_query +------------------ + 135 You can also nest filter expressions within each other: -$.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size() +select jsonb_path_query(:'json'::jsonb, $.track ? (exists(@.segments[*] ? (@.HR > 130))).segments.size()'); + jsonb_path_query +------------------ + 2 This expression returns the size of the track if it contains any segments with high heart rate values, or an empty sequence otherwise. - - PostgreSQL's implementation of the SQL/JSON path - language has the following deviations from the SQL/JSON standard: - + + Devaiations from the SQL Standard + + PostgreSQL's implementation of the SQL/JSON path + language has the following deviations from the SQL/JSON standard: + - - + + Boolean Predicate Path Expressions - A path expression can be a Boolean predicate, although the SQL/JSON - standard allows predicates only in filters. This is necessary for - implementation of the @@ operator. For example, - the following jsonpath expression is valid in - PostgreSQL: + As an extension to the SQL standard, a PostgreSQL + path expression can be a Boolean predicate, whereas the SQL standard allows + predicates only in filters. Where SQL standard path expressions return the + relevant contents of the queried JSON value, predicate path expressions + return the three-value three-valued result of the predicate: + true, false, or + unknown. Compare this filter jsonpath + exression: -$.track.segments[*].HR < 70 +select jsonb_path_query(:'json'::jsonb, '$.track.segments ?(@[*].HR > 130)'); + jsonb_path_query +--------------------------------------------------------------------------------- + {"HR": 135, "location": [47.706, 13.2635], "start time": "2018-10-14 10:39:21"} - - + To a predicate expression, which returns true + +select jsonb_path_query(:'json'::jsonb, '$.track.segments[*].HR > 130'); + jsonb_path_query +------------------ + true + + - - - There are minor differences in the interpretation of regular - expression patterns used in like_regex filters, as - described in . - - - + + Predicate-only path expressions are necessary for implementation of the + @@ operator (and the + jsonb_path_match function), and should not be used + with the @? operator (or + jsonb_path_exists function). + + + + Conversely, non-predicate jsonpath expressions should not be + used with the @@ operator (or the + jsonb_path_match function). + + + + + Regular Expression Interpretation + + There are minor differences in the interpretation of regular + expression patterns used in like_regex filters, as + described in . + + Strict and Lax Modes @@ -17431,18 +17488,30 @@ $.track.segments[*].HR < 70 abstract from the fact that it stores an array of segments when using the lax mode: -lax $.track.segments.location + select jsonb_path_query(:'json'::jsonb, 'lax $.track.segments.location'); + jsonb_path_query +------------------- + [47.763, 13.4034] + [47.706, 13.2635] - In the strict mode, the specified path must exactly match the structure of + In strict mode, the specified path must exactly match the structure of the queried JSON document to return an SQL/JSON item, so using this - path expression will cause an error. To get the same result as in - the lax mode, you have to explicitly unwrap the + path expression will cause an error: + +select jsonb_path_query(:'json'::jsonb, 'strict $.track.segments.location'); +ERROR: jsonpath member accessor can only be applied to an object + + To get the same result as in the lax mode, you have to explicitly unwrap the segments array: -strict $.track.segments[*].location +select jsonb_path_query(:'json'::jsonb, 'strict $.track.segments[*].location'); + jsonb_path_query +------------------- + [47.763, 13.4034] + [47.706, 13.2635] @@ -17451,7 +17520,13 @@ strict $.track.segments[*].location when using the lax mode. For instance, the following query selects every HR value twice: -lax $.**.HR +select jsonb_path_query(:'json'::jsonb, 'lax $.**.HR'); + jsonb_path_query +------------------ + 73 + 135 + 73 + 135 This happens because the .** accessor selects both the segments array and each of its elements, while @@ -17460,7 +17535,11 @@ lax $.**.HR the .** accessor only in the strict mode. The following query selects each HR value just once: -strict $.**.HR +select jsonb_path_query(:'json'::jsonb, 'strict $.**.HR'); + jsonb_path_query +------------------ + 73 + 135 --Apple-Mail=_C167A746-C142-4F05-B098-2EBC6EE99865--