Re: 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].

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

On Wed, Aug 21, 2024 at 3:48 PM zfmohz <zfmohz(at)163(dot)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).