Re: SQL/JSON: functions - Mailing list pgsql-hackers

On Tue, Jan 4, 2022 at 7:32 PM Andrew Dunstan <andrew@dunslane.net> wrote:

I have one general question on the below scenario.
CREATE TABLE T (Id INTEGER PRIMARY KEY,Jcol CHARACTER VARYING ( 5000
)CHECK ( Jcol IS JSON ) );
insert into T values (1,323);
 ORACLE is giving an error(check constraint...violated ORA-06512) for
the above insert but Postgres is allowing it, however is not related
to this patch but just thinking if this is expected.

‘postgres[22198]=#’SELECT * FROM T WHERE Jcol IS JSON;
 id | jcol
----+------
  1 | 323
How come number 323 is the valid json?

Few comments/doubts on 0003-IS-JSON-predicate-v59.patch and
0004-SQL-JSON-query-functions-v59.patch patch:
1) I am not able to find a case where "IS JSON" and "IS JSON VALUE"
gives a different result, is they intended to give the same result(and
two are replaceably used) when applied on any input.

2) Not sure why we return true for the below query?
+-- extension: boolean expressions
+SELECT JSON_EXISTS(jsonb '1', '$ > 2');
+ json_exists
+-------------
+ t
+(1 row)

3)
+-- Strict mode with ERROR on ERROR clause
+SELECT JSON_EXISTS(jsonb '{"a": [1,2,3]}', 'strict $.a[5]' ERROR ON ERROR);
+ERROR: Invalid SQL/JSON subscript

The above example in documentation is not actually matching when I am
trying to run with the patch as below.
‘postgres[28411]=#’SELECT JSON_EXISTS(jsonb '{"a": [1,2,3]}', 'strict
$.a[5]' ERROR ON ERROR);
ERROR:  22033: jsonpath array subscript is out of bounds
LOCATION:  executeItemOptUnwrapTarget, jsonpath_exec.c:769

+SELECT JSON_VALUE('"123.45"', '$' RETURNING float);
+ json_value
+------------
+     123.45
+(1 row)

Above is also not matching:
‘postgres[28411]=#’SELECT JSON_VALUE('"123.45"', '$' RETURNING float);
ERROR:  0A000: JSON_VALUE() is not yet implemented for json type
LINE 1: SELECT JSON_VALUE('"123.45"', '$' RETURNING float);

There is more such example that does not actually produce the same
result when we try to run after applying this patch, seems like we
just need to update the documentation with regards to our new patch.
+SELECT JSON_VALUE(jsonb '[1,2]', 'strict $[*]' ERROR ON ERROR);
+ERROR: more than one SQL/JSON item

‘postgres[28411]=#’SELECT JSON_VALUE(jsonb '[1,2]', 'strict $[*]'
ERROR ON ERROR);
ERROR:  22034: JSON path expression in JSON_VALUE should return
singleton scalar item

4)
index f46786231e..c1951c1caf 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -28,6 +28,7 @@
 #include "catalog/pg_type.h"
 #include "executor/executor.h"
 #include "executor/functions.h"
+#include "executor/execExpr.h"
 #include "funcapi.h"
 #include "miscadmin.h"
 #include "nodes/makefuncs.h"

can we adjust the include file in the alphabetic order please?

5)
+SELECT
+       JSON_QUERY(js, '$'),
+       JSON_QUERY(js, '$' WITHOUT WRAPPER),
+       JSON_QUERY(js, '$' WITH CONDITIONAL WRAPPER),
+       JSON_QUERY(js, '$' WITH UNCONDITIONAL ARRAY WRAPPER),
+       JSON_QUERY(js, '$' WITH ARRAY WRAPPER)
+FROM
+       (VALUES
+               (jsonb 'null'),
+               ('12.3'),
+               ('true'),
+               ('"aaa"'),
+               ('[1, null, "2"]'),
+               ('{"a": 1, "b": [2]}')
+       ) foo(js);
+     json_query     |     json_query     |     json_query     |
json_query      |      json_query
+--------------------+--------------------+--------------------+----------------------+----------------------
+ null               | null               | [null]             |
[null]               | [null]
+ 12.3               | 12.3               | [12.3]             |
[12.3]               | [12.3]
+ true               | true               | [true]             |
[true]               | [true]
+ "aaa"              | "aaa"              | ["aaa"]            |
["aaa"]              | ["aaa"]
+ [1, null, "2"]     | [1, null, "2"]     | [1, null, "2"]     | [[1,
null, "2"]]     | [[1, null, "2"]]
+ {"a": 1, "b": [2]} | {"a": 1, "b": [2]} | {"a": 1, "b": [2]} |
[{"a": 1, "b": [2]}] | [{"a": 1, "b": [2]}]
+(6 rows)

Just a suggestion if we can have column aliases for better
understanding like we are doing for other test cases in the same
patch?

--
Regards,
Himanshu Upadhyaya
EnterpriseDB: http://www.enterprisedb.com