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: 255 bytes each.

• SQL/JSON functions and dot-notation syntax

  • SQL/JSON path length: 32K bytes.

    See Overview of SQL/JSON Path Expressions for general information about SQL/JSON path expressions.

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

    • 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