On Thu, Sep 16, 2021 at 8:23 PM Andrew Dunstan <and...@dunslane.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