The json_table function returns an incorrect column type

Hi

The JSON_OBJECT is by default formatting as text, adding explicit format
type to JSON_OBJECT will solve the problem.

For example

postgres=# SELECT json_object('configd' value item format json) FROM
JSON_TABLE('{"empno":1001}', '$' COLUMNS (item text FORMAT JSON PATH '$'));
json_object
-------------------------------
{"configd" : {"empno": 1001}}
(1 row)

postgres=# SELECT json_object('configd' value item) FROM
JSON_TABLE('{"empno":1001}', '$' COLUMNS (item text FORMAT JSON PATH '$'));
json_object
-----------------------------------
{"configd" : "{\"empno\": 1001}"}
(1 row)

I changed the default_format for JSON_OBJECT here[1]https://github.com/postgres/postgres/blob/4baff5013277a61f6d5e1e3369ae3f878cb48d0a/src/backend/parser/parse_expr.c#L3723.

Node *val = transformJsonValueExpr(pstate, "JSON_OBJECT()",
kv->value,
JS_FORMAT_JSON,
InvalidOid, false);

This solves the problem but some tests are still failing. Don't know
whether the default format should be JSON(looks like oracle did something
like this ) or text However, just sharing some findings here.

Thanks
Imran Zaheer

[1]: https://github.com/postgres/postgres/blob/4baff5013277a61f6d5e1e3369ae3f878cb48d0a/src/backend/parser/parse_expr.c#L3723
https://github.com/postgres/postgres/blob/4baff5013277a61f6d5e1e3369ae3f878cb48d0a/src/backend/parser/parse_expr.c#L3723

On Wed, Aug 21, 2024 at 3:48 PM zfmohz <zfmohz@163.com> wrote:

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).