JSON Data Type

JSON is a lightweight text-based open standard designed for human-readable data interchange. It is easy to read and write, and language independent. A JSON text is data represented in name/value format. A valid JSON text contains an object surrounded by curly brackets and includes a comma-separated list of name/value pairs. Each name is followed by a ':'(colon) character. JSON data is written to JSON data columns by providing a JSON object.

Table 2-4 JSON Data Type

Data Type	Description	Examples
JSON	The JSON type represents all valid JSON values. Specifically, an instance of JSON can be an instance of ANYJSONATOMIC, or an array whose elements are all instances of JSON, or a map whose field values are all instances of JSON.	{ "city" : "Santa Cruz", "zip" : 95008, "phones" : [ { "area" : 408, "number" : 4538955, "kind" : "work" }, { "area" : 831, "number" : 7533341, "kind" : "home" } ] } "Santa Cruz" 95008 true [ 12, "foo", { "city":"Santa Cruz"}, [2, 3]]

Data Type

Description

Examples

JSON

The JSON type represents all valid JSON values. Specifically, an instance of JSON can be

an instance of ANYJSONATOMIC,
or an array whose elements are all instances of JSON,
or a map whose field values are all instances of JSON.

{ "city" : "Santa Cruz", "zip" : 95008, "phones" : [ { "area" : 408, "number" : 4538955, "kind" : "work" }, { "area" : 831, "number" : 7533341, "kind" : "home" } ] }

"Santa Cruz"

95008

true

[ 12, "foo", { "city":"Santa Cruz"}, [2, 3]]

To load JSON data into a table, Oracle NoSQL Database offers programmatic APIs to ingest JSON text. The supported NoSQL SDKs handle this differently. Oracle NoSQL Database will parse the input JSON text internally and map its constituent pieces to the types described as follows:

Numbers are converted to integer, long, or double items, depending on the actual value of the number (float items are not used for JSON).
Strings in the input text are mapped to string items.
Boolean values are mapped to boolean items.
The null values are mapped to JSON nulls depending on the way the input is supplied. For example, If you supply input text as "myvalue" : null, this is mapped as a JSON null. Whereas, supplying "myvalue" : "null" sets the string value to the text "null".
When an array is encountered in the input text, an array item is created whose type is Array(JSON). This is done unconditionally, no matter what the actual contents of the array might be.
When a JSON object is encountered in the input text, a map item is created whose type is Map(JSON), unconditionally.

In general, the result of this parsing is a tree of maps, arrays, and atomic values. For persistent storage, the tree is serialized into a binary format.

Note:

There is no JSON equivalent to the TIMESTAMP data type, so if input text contains a string in the TIMESTAMP format, it is stored as a string item in the JSON column.

JSON data is schema-less, in the sense that a field of type JSON can have very different kinds of values in different table rows. For example, if info is a top-level table column of type JSON, in one row the value of info may be an integer, in another row an array containing a mix of doubles and strings, and in a third row a map containing a mix of other maps, arrays, and atomic values. Furthermore, the data stored in a JSON column or field can be updated in any way that still produces a valid JSON instance. As a result, each JSON tree (either in main memory or as a serialized byte array on disk) is self-describing about its contents.