From: | Imran Zaheer <imran(dot)zhir(at)gmail(dot)com> |
---|---|
To: | zfmohz <zfmohz(at)163(dot)com> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: The json_table function returns an incorrect column type |
Date: | 2024-08-21 13:45:41 |
Message-ID: | CA+UBfakySbuirmZCHV+bO2dywJ3psNL2QYphLpg6hgAT3AfKUA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
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).
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2024-08-21 14:00:40 | Re: Requiring LLVM 14+ in PostgreSQL 18 |
Previous Message | Peter Eisentraut | 2024-08-21 13:34:19 | Re: Cirrus CI for macOS branches 16 and 15 broken |