Experimenting with JSON

JavaScript Object Notation (JSON) is a lightweight programming independent data interchange format. It is mainly used in web applications to transmit information. It is not only simple for humans to read but easy for machines to parse and generate. JSON has some advantages for traditional extensible markup language (XML):

• XML data is a typeless only string, whereas JSON data is typed like string, number, array, and boolean
• JSON data is readily accessible, whereas XML data needs to be parsed and assigned to variables

The JSON syntax is a subset of the JavaScript syntax. There are some rules when it comes to JSON syntax:

• Data is defined in name/value pairs
• Data is separated by commas
• Curly braces hold objects
• Square brackets hold arrays

{
"name":"Robert"
}

We can use JSON as we would use any other SQL data type:

• JSON content is stored in databases and optimized depending on the
  size of the data.
• The user is not responsible for executing the CREATE TYPE statement for the JSON data type. JSON data types do not need to be created via DDL by the user as the JSON data type exists in the database.
• The JSON data type cannot be created, dropped, or altered by the user.

The following is the syntax for the JSON data type:

JSON [integer] Character Set[Unicode/latin] Attribute

This table highlights the type integer length based on the character set chosen:

JSON also carries attributes like any other SQL datatype; it can be or have the following:

• Could be NULL and NOT NULL
• Can have FORMAT
• Can have TITLE
• Could be NAMED
• Could be DEFAULT NULL
• Could be COMPRESS USING and DECOMPRESS USING

JSON data is stored inline and in LOB subtables depending on the size of the data.

In this recipe, we will create a JSON table, insert values into it, and select and collect statistics on the JSON column.