Teach jsonpath string() to unwrap in lax mode

adunstan · adunstan · commit 653d3969bb01 · 2024-06-17T10:31:29.000-04:00
This was an ommission in commit 66ea94e, and brings it into compliance with both other methods and the standard. Per complaint from David Wheeler. Author: David Wheeler, Jeevan Chalke Reviewed-by: Chapman Flack Discussion: https://postgr.es/m/A64AE04F-4410-42B7-A141-7A7349260F4D@justatheory.com
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
@@ -17792,7 +17792,10 @@ ERROR:  jsonpath member accessor can only be applied to an object
     methods available in <type>jsonpath</type>.  Note that while the unary
     operators and methods can be applied to multiple values resulting from a
     preceding path step, the binary operators (addition etc.) can only be
-    applied to single values.
+    applied to single values.  In lax mode, methods applied to an array will be
+    executed for each value in the array.  The exceptions are
+    <literal>.type()</literal> and <literal>.size()</literal>, which apply to
+    the array itself.
    </para>
 
    <table id="functions-sqljson-op-table">
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
@@ -1606,6 +1606,9 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
 				JsonbValue	jbv;
 				char	   *tmp = NULL;
 
+				if (unwrap && JsonbType(jb) == jbvArray)
+					return executeItemUnwrapTargetArray(cxt, jsp, jb, found, false);
+
 				switch (JsonbType(jb))
 				{
 					case jbvString:
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2525,7 +2525,10 @@ select jsonb_path_query('null', '$.string()', silent => true);
 (0 rows)
 
 select jsonb_path_query('[]', '$.string()');
-ERROR:  jsonpath item method .string() can only be applied to a bool, string, numeric, or datetime value
+ jsonb_path_query 
+------------------
+(0 rows)
+
 select jsonb_path_query('[]', 'strict $.string()');
 ERROR:  jsonpath item method .string() can only be applied to a bool, string, numeric, or datetime value
 select jsonb_path_query('{}', '$.string()');
@@ -2576,6 +2579,13 @@ select jsonb_path_query('1234', '$.string().type()');
  "string"
 (1 row)
 
+select jsonb_path_query('[2, true]', '$.string()');
+ jsonb_path_query 
+------------------
+ "2"
+ "true"
+(2 rows)
+
 select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()');
 ERROR:  cannot convert value from timestamptz to timestamp without time zone usage
 HINT:  Use *_tz() function for time zone support.
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -586,6 +586,7 @@ select jsonb_path_query('"1.23aaa"', '$.string()');
 select jsonb_path_query('1234', '$.string()');
 select jsonb_path_query('true', '$.string()');
 select jsonb_path_query('1234', '$.string().type()');
+select jsonb_path_query('[2, true]', '$.string()');
 select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()');
 select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); -- should work
 select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string()');

Original file line number	Diff line number	Diff line change
`@@ -1606,6 +1606,9 @@ executeItemOptUnwrapTarget(JsonPathExecContext cxt, JsonPathItem jsp,`
`1606`	`1606`	`JsonbValue jbv;`
`1607`	`1607`	`char *tmp = NULL;`
`1608`	`1608`
	`1609`	`+ if (unwrap && JsonbType(jb) == jbvArray)`
	`1610`	`+ return executeItemUnwrapTargetArray(cxt, jsp, jb, found, false);`
	`1611`	`+`
`1609`	`1612`	`switch (JsonbType(jb))`
`1610`	`1613`	`{`
`1611`	`1614`	`case jbvString:`