A Oracle Database JSON Restrictions

The restrictions associated with Oracle support of JSON data in Oracle Database are listed here.

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

  • General

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

    • JSON field name length: 32767 bytes, maximum.

  • SQL/JSON functions

    • Return-value length: 32767 bytes, maximum.

    • Path length: 4K bytes, maximum.

    • Number of path steps: 65535, maximum.

  • Simplified JSON syntax

    • Path length: 4K bytes, maximum.

    • Path component length: 128 bytes, maximum.

  • JSON search index

    • Field name length: 64 bytes, maximum. If a document has a field name longer than 64 bytes, it might not be completely indexed, and in that case an error is recorded in database view CTX_USER_INDEX_ERRORS.

  • JSON data guide

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

    • Number of children under a parent node: 65535, maximum. A node that has more than 65535 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.

    • Zero-length field name: A zero-length (empty) object field name ("") is not supported for use with JSON data guide. Data-guide behavior is undefined for JSON data that contains such a name.

  • OSON

    • Field name length: 255 bytes, maximum.

    • No duplicate fields: If a JSON object with duplicate field names is represented using OSON then only one of these fields is present (kept).

  • No offload of Hadoop-based external-table LOB data to Oracle Big Data SQL

    • JSON data that is stored in an external table based on a Hadoop Hive table is not offloaded to Oracle Big Data SQL when LOB storage is used. See Oracle Big Data SQL User's Guide.

  • You cannot query JSON data across multiple shards unless it is stored as VARCHAR2.