B Oracle Database JSON Capabilities Specification

This appendix specifies capabilities for Oracle support of JSON data in Oracle Database.

Unless otherwise specified, an error is raised if a specification is not respected.

• General:

  • Number of nesting levels for a JSON object or array: 1000.

  • JSON field name length: 65535 bytes each.

• SQL/JSON path expressions:

  • Each use of a string in a SQL/JSON path expression: 32K bytes, whether the string is literal or is provided by a SQL/JSON variable, such as $a. (32K bytes is the maximum length of a SQL VARCHAR2 value.)

  • Total length of path-expression text: 32K bytes. (It is passed as a string to SQL operators such as json_exists.)

    The effective length of a path expression is essentially unlimited, because the expression can make use of SQL/JSON variables that are bound to string values, each of which is limited to 32K bytes.

  See also Overview of SQL/JSON Path Expressions.

• Component length for dot-notation paths: 128 bytes. (This is the maximum length of a SQL identifier.)

  See also:

  • Oracle Database Object-Relational Developer's Guide for information about SQL dot-notation syntax

  • Oracle Database SQL Language Reference for information about SQL identifiers

• JSON data guide:

  Note:

  • Path length: 4000 bytes. A path longer than 4000 bytes is ignored by a data guide.

  • Number of children under a parent node: 5000. A node that has more than 5000 children is ignored by a data guide.

  • Field value length: 32767 bytes. If a JSON field has a value longer than 32767 bytes then the data guide reports the length as 32767.

  • Data-guide behavior is undefined for data that contains zero-length (empty) object field name ("").

  See Overview of JSON Data Guide for more information about JSON data guide.

• OSON and JSON data type:

  OSON is Oracle's optimized binary JSON format for query and update in both Oracle Database server and Oracle Database clients. An instance of JSON data type is stored using format OSON.

  • Total size of a JSON type instance: 32M bytes.

    See Data Types for JSON Data for more information about the storage of JSON data as JSON type