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

 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)

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?