When testing the json_table function, it was discovered that specifying FORMAT
JSON in the column definition clause and applying this column to the
JSON_OBJECT function results in an output that differs from Oracle's output.
The sql statement is as follows:
SELECT JSON_OBJECT('config' VALUE config)
FROM JSON_TABLE(
'[{"type":1, "order":1, "config":{"empno":1001, "ename":"Smith",
"job":"CLERK", "sal":1000}}]',
'$[*]' COLUMNS (
config varchar(100) FORMAT JSON PATH '$.config'
)
);
The execution results of postgresql are as follows:
json_object
-------------------------------------------------------------------------------------------
{"config" : "{\"job\": \"CLERK\", \"sal\": 1000, \"empno\": 1001, \"ename\":
\"Smith\"}"}
(1 row)
The execution results of oracle are as follows:
JSON_OBJECT('CONFIG'VALUECONFIG)
---------------------------------------------------------------------
{"config":{"empno":1001,"ename":"Smith","job":"CLERK","sal":1000}}
1 row selected.
Elapsed: 00:00:00.00
In PostgreSQL, the return value of the json_table function is treated as plain
text, and double quotes are escaped with a backslash. In Oracle, the return
value of the json_table function is treated as a JSON document, and the double
quotes within it are not escaped with a backslash.
Based on the above observation, if the FORMAT JSON option is specified in the
column definition clause of the json_table function, the return type should be
JSON, rather than a specified type like VARCHAR(100).
