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.