The json_table function returns an incorrect column type

Поиск
Список
Период
Сортировка
От zfmohz
Тема The json_table function returns an incorrect column type
Дата
Msg-id 36a0097e.165.19173b1a7de.Coremail.zfmohz@163.com
обсуждение исходный текст
Ответы Re: The json_table function returns an incorrect column type
Список pgsql-hackers
Дерево обсуждения
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).

В списке pgsql-hackers по дате отправления: