Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Documenation bug in new 23c SORT_OP of JSON_TRANSFORM

The new sort operation in JSON_TRANSFORM is documented in the SQL Language Reference. See https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/JSON_TRANSFORM.html#GUID-DD2A821B-C688-4310-81B5-5F45090B9366__GUID-4C64E47C-91AA-472B-B94A-FD2BAAAFD9EB

According to this documentation, the following should work:

select json_transform('{"a":[1,2,3,3,4,4]}',
          desc unique
       );

but it throws an SQL Error: ORA-40789: JSON_TRANSFORM operation invalid… error. Because no operation is defined.

The following is the correct statement for the lower branch:

select json_transform('{"a":[1,2,3,3,4,4]}',
          sort '$.a' desc unique
       );

So SORT path_expr is required for the lower branch as well.

This post has been answered by gsalem-Oracle on Jan 3 2024
Jump to Answer
Comments
Post Details
Added on Jan 2 2024
1 comment
135 views