JSON was initially developed to exchange data via RESTful APIs (Representative State Transfer Application Programming Interface). The encoding is always Unicode, mostly UTF8. Programmable Web contains a variety of links to APIs like Twitter, LinkedIn, Strava, GitHub. JSON is simpler and more compact compared to XML. JSON is replacing XML more and more to exchange data. JSON is used

  • for data exchange
  • as a configuration file (for example, Node.js stores metadata in package.json)
  • as a primary storage format in databases like MongoDB

JSON was developed by Douglas Crockford in 2001 and is standardized by

  • IETF (Internet Engineering Task Force) RFC 7158 and RFC 7159
  • ECMA (European Computer Manufacturers Association) ECMA 404

The following example shows a JSON document created using https://jsoneditoronline.org/.

JSON documents include JSON data types and JSON value types. There are three JSON data types:

  • Key-value pairs that contain a key and an associated value
    In the example, “customerID”: 4711 is a key-value pair
  • Objects that contain an unordered list of key-value pairs
    Objects are enclosed in an opening ({) and a closing parenthesis (}) and contain key-value pairs. Objects can be nested. The example shows two customer objects.
  • Arrays containing ordered values
    Arrays are enclosed by an opening ([) and a closing parenthesis (]) and contain values or objects

JSON value types are on the right side of a key-value pair. The following JSON value types are distinguished:

  • object
    see above
  • array
    see above
  • string
    Strings are enclosed in quotes (“…”). Simple quotes (‘…’) are not allowed.
  • number
    Numbers are not enclosed in single quotes and may contain a period (.) As a decimal separator.
  • boolean
    Defined by the two values true and false.

Also, a value can be null. JSON documents contain no comments.

Google released a comprehensive JSON style guide that is mandatory for developing JSON APIs at Google. Examples of guidelines for Google APIs:

  • In key-value pairs, the key is read lowerCamelCase (eg: customerID) and not as snake_case (eg: customer_id)
  • Dates are given following RFC 3339, e.g. “OrderDate”: “2010-12-01T15: 12: 15-05: 00” with a time offset of -5 hours compared to UTC / GMT time zone

SQL and JSON

JSON is the primary storage format of many NoSQL databases like MongoDB. But even relational databases can handle JSON documents very well. JSON has been included in the ISO SQL Standard 2017 as Part 6 – SQL support for JavaScript Object Notation (JSON): ISO / IEC TR 19075-6: 2017.
A central design criterion of JSON is the renunciation of a scheme. A JSON object can be arbitrarily changed and key-value pairs added with keys that were not yet considered at the time of object creation. The schema will only be applied when the data gets read (schema-on-read). JSON documents are self-descriptive; Metadata is part of a JSON document. At http://json-schema.org an attempt is made to validate a schema syntactically. However, the general interest currently seems to be very low in such a validation.
The ISO SQL standard distinguishes three use cases:

  • Acquisition and storage of JSON
    The ISO SQL standard provides storage as a string or in binary format in a table column.
  • Generation of JSON from data in tables
    See the constructor functions below.
  • Query saved JSON data
    See the query functions below.

The ISO SQL standard defines the following constructor functions and predicates:

  • JSON_OBJECT or JSON_OBJECTAGG
    Creates a JSON object.
  • JSON_ARRAY or JSON_ARRAYAGG
    Creates a JSON array.
  • IS_JSON
    Check if a JSON document is syntactically correct or not.

The ISO SQL standard defines the following query functions:

  • JSON_EXISTS
    Check for the existence of a searched value.
  • JSON_VALUE
    Extracts a scalar value.
  • JSON_QUERY
    Extracts an SQL / JSON value.
  • JSON_TABLE
    Extracts a table with rows and columns.

The ISO SQL standard also provides two modes:

  • Lax mode
    If the JSON document is structurally incorrect, or if the query is structurally incorrect, the error is ignored (and, for example, zero is returned as a result).
  • Strict mode
    If the JSON document is structurally incorrect or if the query is structurally incorrect, an error is generated.