JSON Data (Standard) (2024)

Standard JSON values, scalars, objects, and arrays are described.

According to the JSON standard, a JSON value is one of the following JSON-language data types: object, array, number, string, Boolean (value true or false), or null (value null). All values except objects and arrays are scalar.

Note:

A JSON value of null is a value as far as SQL is concerned. It is not NULL, which in SQL represents the absence of a value (missing, unknown, or inapplicable data). In particular, SQL condition IS NULL returns false for a JSON null value, and SQL condition IS NOT NULL returns true.

Standard JSON has no date data type (unlike both XML and JavaScript). A date is represented in standard JSON using the available standard data types, such as string. There are some de facto standards for converting between dates and JSON strings. But typically programs using standard JSON data must, one way or another, deal with date representation conversion.

A JavaScript object is an associative array, or dictionary, of zero or more pairs of property names and associated JSON values.Foot2 A JSON object is a JavaScript object literal.Foot3 It is written as such a property list enclosed in braces ({, }), with name–value pairs separated by commas (,), and with the name and value of each pair separated by a colon (:). (Whitespace before or after the comma or colon is optional and insignificant.)

In JSON each property name and each string value must be enclosed in double quotation marks ("). In JavaScript notation, a property name used in an object literal can be, but need not be, enclosed in double quotation marks. It can also be enclosed in single quotation marks (').

As a result of this difference, in practice, data that is represented using unquoted or single-quoted property names is sometimes referred to loosely as being represented in JSON, and some implementations of JSON, including the Oracle Database implementation, support the lax syntax that allows the use of unquoted and single-quoted property names.

A string in JSON is composed of Unicode characters, with backslash (\) escaping. A JSON number (numeral) is represented in decimal notation, possibly signed and possibly including a decimal exponent.

An object property is typically called a field. It is sometimes called a key, but this documentation generally uses “field” to avoid confusion with other uses here of the word “key”. An object property name–value pair is often called an object member (but sometimes member can mean just the property). Order is not significant among object members.

Note:

A JSON field name can be empty (written "").Foot4
See Also
JSON Object Structure
Each field name in a given JSON object is not necessarily unique; the same field name can be repeated. The SQL/JSON path evaluation that Oracle Database employs always uses only one of the object members that have a given field name; any other members with the same name are ignored. It is unspecified which of multiple such members is used.

See also Unique Versus Duplicate Fields in JSON Objects.

A JavaScript array has zero or more elements. A JSON array is represented by brackets ([, ]) surrounding the representations of the array elements (also called items), which are separated by commas (,), and each of which is an object, an array, or a scalar value. Array element order is significant. (Whitespace before or after a bracket or comma is optional and insignificant.)

Example 1-1 A JSON Object (Representation of a JavaScript Object Literal)

This example shows a JSON object that represents a purchase order, with top-level field names PONumber, Reference, Requestor, User, CostCenter, ShippingInstruction, Special Instructions, AllowPartialShipment and LineItems.

{ "PONumber" : 1600, "Reference" : "ABULL-20140421", "Requestor" : "Alexis Bull", "User" : "ABULL", "CostCenter" : "A50", "ShippingInstructions" : { "name" : "Alexis Bull", "Address": { "street" : "200 Sporting Green", "city" : "South San Francisco", "state" : "CA", "zipCode" : 99236, "country" : "United States of America" }, "Phone" : [ { "type" : "Office", "number" : "909-555-7307" }, { "type" : "Mobile", "number" : "415-555-1234" } ] }, "Special Instructions" : null, "AllowPartialShipment" : false, "LineItems" : [ { "ItemNumber" : 1, "Part" : { "Description" : "One Magic Christmas", "UnitPrice" : 19.95, "UPCCode" : 13131092899 }, "Quantity" : 9.0 }, { "ItemNumber" : 2, "Part" : { "Description" : "Lethal Weapon", "UnitPrice" : 19.95, "UPCCode" : 85391628927 }, "Quantity" : 5.0 } ] }

Most of the fields here have string values. For example: field User has value "ABULL".
Fields PONumber and zipCode have numeric values: 1600 and 99236.
Field ShippingInstructions has an object as its value. This object has three members, with fields name, Address, and Phone. Field name has a string value ("Alexis Bull").
The value of field Address is an object with fields street, city, state, zipCode, and country. Field zipCode has a numeric value; the others have string values.
Field Phone has an array as value. This array has two elements, each of which is an object. Each of these objects has two members: fields type and number with their values.
Field Special Instructions has a null value.
Field AllowPartialShipment has the Boolean value false.
Field LineItems has an array as value. This array has two elements, each of which is an object. Each of these objects has three members, with fields ItemNumber, Part, and Quantity.
Fields ItemNumber and Quantity have numeric values. Field Part has an object as value, with fields Description, UnitPrice, and UPCCode. Field Description has a string value. Fields UnitPrice and UPCCode have numeric values.