Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. doc: remove llvm-config search from configure documentation

  1. The json_table function returns an incorrect column type

    zfmohz <zfmohz@163.com> — 2024-08-21T06:48:28Z

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

  2. Re: The json_table function returns an incorrect column type

    Imran Zaheer <imran.zhir@gmail.com> — 2024-08-21T13:45:41Z

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