Re: SQL/JSON: functions

On Thu, Sep 16, 2021 at 8:23 PM Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

>
> On 9/14/21 8:55 AM, Andrew Dunstan wrote:
>

I have tried with few of the test cases of constructor function, wanted to
check on the below scenarios:

1)
Why we don't support KEY(however is optional as per SQL standard) keyword?
SELECT JSON_OBJECT(KEY 'a' VALUE '123');
ERROR: type "key" does not exist
LINE 1: SELECT JSON_OBJECT(KEY 'a' VALUE '123');

ORACLE is supporting the above syntax.

I can see TODO as below
+json_name_and_value:
+/* TODO This is not supported due to conflicts
+ KEY c_expr VALUE_P json_value_expr %prec POSTFIXOP
+ { $$ = makeJsonKeyValue($2, $4); }
+ |
+*/

but still not very clear what kind of conflict we are mentioning here, also
any plan of finding a solution to that conflict?

2)
I am not sure if below is required as per SQL standard, ORACLE is allowing
to construct JSON_OBJECT bases on the records in the table as below, but
postgres parser is not allowing:
create table test (id varchar(10), value int);
insert into test values ('a',1);
insert into test values ('b',2);
insert into test values ('c',3);
select json_object(*) from test; --postgres does not support
postgres=# select json_object(*) from test;
ERROR: syntax error at or near "*"
LINE 1: select json_object(*) from test;

3)
Is not that result of the two below queries should match because both are
trying to retrieve the information from the JSON object.

postgres=# SELECT JSON_OBJECT('track' VALUE '{
"segments": [
{
"location": [ 47.763, 13.4034 ],
"start time": "2018-10-14 10:05:14",
"HR": 73
},
{
"location": [ 47.706, 13.2635 ],
"start time": "2018-10-14 101:39:21",
"HR": 135
}
]
}
}')->'track'->'segments';
?column?
----------

(1 row)

postgres=# select '{
"track": {
"segments": [
{
"location": [ 47.763, 13.4034 ],
"start time": "2018-10-14 10:05:14",
"HR": 73
},
{
"location": [ 47.706, 13.2635 ],
"start time": "2018-10-14 10:39:21",
"HR": 135
}
]
}
}'::jsonb->'track'->'segments';

?column?
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
[{"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"}]
(1 row)

4)
Are we intentionally allowing numeric keys in JSON_OBJECT but somehow these
are not allowed in ORACLE?
‘postgres[151876]=#’select JSON_OBJECT( 3+1:2, 2+2:1);
json_object
--------------------
{"4" : 2, "4" : 1}
(1 row)

In ORACLE we are getting error("ORA-00932: inconsistent datatypes: expected
CHAR got NUMBER") which seems to be more reasonable.
"ORA-00932: inconsistent datatypes: expected CHAR got NUMBER"

Postgres is also dis-allowing below then why allow numeric keys in
JSON_OBJECT?
‘postgres[151876]=#’select '{
"track": {
"segments": [
{
"location": [ 47.763, 13.4034 ],
"start time": "2018-10-14 10:05:14",
"HR": 73
},
{
"location": [ 47.706, 13.2635 ],
"start time": "2018-10-14 10:39:21",
3: 135
}
]
}
}'::jsonb;
ERROR: 22P02: invalid input syntax for type json
LINE 1: select '{
^
DETAIL: Expected string, but found "3".
CONTEXT: JSON data, line 12: 3...
LOCATION: json_ereport_error, jsonfuncs.c:621

Also, JSON_OBJECTAGG is failing if we have any numeric key, however, the
message is not very appropriate.
SELECT JSON_OBJECTAGG(k: v ABSENT ON NULL) AS apt
FROM (VALUES ('no', 5), ('area', 50), ('rooms', 2), ('foo', NULL), (5,5))
kv(k, v);
ERROR: 22P02: invalid input syntax for type integer: "no"
LINE 2: FROM (VALUES ('no', 5), ('area', 50), ('rooms', 2), ('foo', ...
^
LOCATION: pg_strtoint32, numutils.c:320

Few comments For 0002-SQL-JSON-constructors-v59.patch:
1)
+ if (IsA(node, JsonConstructorExpr))
+ {
+ JsonConstructorExpr *ctor = (JsonConstructorExpr *) node;
+ ListCell *lc;
+ bool is_jsonb =
+ ctor->returning->format->format == JS_FORMAT_JSONB;
+
+ /* Check argument_type => json[b] conversions */
+ foreach(lc, ctor->args)
+ {
+ Oid typid =
exprType(lfirst(lc));
+
+ if (is_jsonb ?
+ !to_jsonb_is_immutable(typid) :
+ !to_json_is_immutable(typid))
+ return true;
+ }
+
+ /* Check all subnodes */
+ }
can have ctor as const pointer?

2)
+typedef struct JsonFormat
+{
+ NodeTag type;
+ JsonFormatType format; /* format type */
+ JsonEncoding encoding; /* JSON encoding */
+ int location; /* token location,
or -1 if unknown */
+} JsonFormat;

I think it will be good if we can have a JsonformatType(defined in patch
0001-Common-SQL-JSON-clauses-v59.patch) member named as
format_type or formatType instead of format?
There are places in the patch where we access it as "if (format->format ==
JS_FORMAT_DEFAULT)". "format->format" looks little difficult to understand.
"format->format_type == JS_FORMAT_DEFAULT" will be easy to follow.

3)
+ if (have_jsonb)
+ {
+ returning->typid = JSONBOID;
+ returning->format->format = JS_FORMAT_JSONB;
+ }
+ else if (have_json)
+ {
+ returning->typid = JSONOID;
+ returning->format->format = JS_FORMAT_JSON;
+ }
+ else
+ {
+ /* XXX TEXT is default by the standard, but we
return JSON */
+ returning->typid = JSONOID;
+ returning->format->format = JS_FORMAT_JSON;
+ }

why we need a separate "else if (have_json)" statement in the below code,
"else" is also doing the same thing?

4)
-test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath
+test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath
sqljson

can we rename sqljson sql test file to json_constructor?

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