Oracle JSON Document Support and MySQL JSON

Oracle Usage

JSON documents are based on JavaScript syntax and allow the serialization of objects. Oracle support for JSON document storage and retrieval enables you to extend the database capabilities beyond purely relational use cases and allows an Oracle database to support semi-structured data. Oracle JSON support also includes full-text search and several other functions dedicated to querying JSON documents.

Oracle 19 adds a new JSON_SERIALIZE function. You can use this function to serialize JSON objects to text.

For more information, see Introduction to JSON Data and Oracle Database in the Oracle documentation.

Examples

The following example creates a table to store a JSON document in a data column and insert a JSON document into the table.

CREATE TABLE json_docs (id RAW(16) NOT NULL, data CLOB,
CONSTRAINT json_docs_pk PRIMARY KEY (id),
CONSTRAINT json_docs_json_chk CHECK (data IS JSON));

INSERT INTO json_docs (id, data) VALUES (SYS_GUID(),
'{
  "FName" : "John",
  "LName" : "Doe",
  "Address" : {
    "Street" : "101 Street",
    "City" : "City Name",
    "Country" : "US",
    "Pcode" : "90210"}
}');

Unlike XML data, which is stored using the XMLType SQL data type, JSON data is stored in an Oracle Database using the SQL data types VARCHAR2, CLOB, and BLOB. Oracle recommends that you always use an is_json check constraint to ensure the column values are valid JSON instances. Or, add a constraint at the table-level CONSTRAINT json_docs_json_chk CHECK (data IS JSON).

You can query a JSON document directly from a SQL query without the use of special functions. Querying without functions is called Dot Notation.

SELECT a.data.FName,a.data.LName,a.data.Address.Pcode AS Postcode
FROM json_docs a;

FNAME  LNAME  POSTCODE
John   Doe    90210

1 row selected.

In addition, Oracle provides multiple SQL functions that integrate with the SQL language and enable querying JSON documents (such as IS JSON, JSON_VAUE, JSON_EXISTS, JSON_QUERY, and JSON_TABLE).

For more information, see Introduction to JSON Data and Oracle Database in the Oracle documentation.

MySQL Usage

Aurora MySQL 5.7 supports a native JSON data type for storing JSON documents, which provides several benefits over storing the same document as a generic string. All JSON documents stored as a JSON data type are validated for correctness. If the document is not valid JSON, it is rejected and an error condition is raised. In addition, more efficient storage algorithms enable optimized read access to elements within the document. The optimized internal binary representation of the document enables much faster operation on the data without requiring expensive re-parsing.

Consider the following example:

CREATE TABLE JSONTable (
  DocumentIdentifier INT NOT NULL PRIMARY KEY,
  JSONDocument JSON);

MySQL 5.7.22 also added the JSON utility function JSON_PRETTY() which outputs an existing JSON value in an easy-to-read format; each JSON object member or array value is printed on a separate line and a child object or array is indented two spaces with respect to its parent. This function also works with a string that can be parsed as a JSON value. For more information, see JSON Utility Functions in the MySQL documentation.

MySQL 5.7.22 also added the JSON utility functions JSON_STORAGE_SIZE() and JSON_STORAGE_FREE().

JSON_STORAGE_SIZE() returns the storage space in bytes used for the binary representation of a JSON document prior to any partial update.

JSON_STORAGE_FREE() shows the amount of space freed after it has been partially updated using JSON_SET() or JSON_REPLACE(). This is greater than zero if the binary representation of the new value is less than that of the previous value. Each of these functions also accepts a valid string representation of a JSON document. For such a value JSON_STORAGE_SIZE() returns the space used by its binary representation following its conversion to a JSON document. For a variable containing the string representation of a JSON document JSON_STORAGE_FREE() returns zero.

These functions produce an error if the non-null argument can’t be parsed as a valid JSON document and NULL if the argument is NULL. For more information, see JSON Utility Functions in the MySQL documentation.

JSON Functions

Aurora MySQL supports a rich set of more than 25 targeted functions for working with JSON data. These functions enable adding, modifying, and searching JSON data. Additionally, spatial JSON functions can be used for GeoJSON documents. For more information, see Spatial GeoJSON Functions in the MySQL documentation.

The JSON_ARRAY, JSON_OBJECT, and JSON_QUOTE functions all return a JSON document from a list of values, a list of key-value pairs, or a JSON value respectively.

Consider the following example:

SELECT JSON_OBJECT('Person', 'John', 'Country', 'USA');
{"Person": "John", "Country": "USA"}

The JSON_CONTAINS, JSON_CONTAINS_PATH, JSON_EXTRACT, JSON_KEYS, and JSON_SEARCH functions are used to query and search the content of a JSON document.

The CONTAINS functions are Boolean functions that return 1 or 0 (TRUE or FALSE).

JSON_EXTRACT returns a subset of the document based on the XPATH expression.

JSON_KEYS returns a JSON array consisting of the top-level key (or path top level) values of a JSON document.

The JSON_SEARCH function returns the path to one or all of the instances of the search string.

Examples

SELECT JSON_EXTRACT('["Mary", "Paul", ["Jim", "Ryan"]]', '$[1]');

"Paul"

SELECT JSON_SEARCH('["Mary", "Paul", ["Jim", "Ryan"]]', 'one', 'Paul');

"$[1]"

Aurora MySQL supports the following functions for adding, deleting, and modifying JSON data: JSON_INSERT, JSON_REMOVE, JSON_REPLACE, and their ARRAY counterparts, which are used to create, delete, and replace existing data elements.

SELECT JSON_ARRAY_INSERT('["Mary", "Paul", "Jim"]', '$[1]', 'Jack');

["Mary", "Jack", "Paul", "Jim"]

JSON_SEARCH is used to find the location of an element value within a JSON document.

SELECT JSON_SEARCH('["Mary", "Paul", ["Jim", "Ryan"]]', 'one', 'Paul');

"$[1]"

JSON Indexes

JSON columns are effectively a BINARY family type, which cannot be indexed. As an alternative, use CREATE TABLE or ALTER TABLE to add generated columns that represent some value from the JSON document and create an index on the generated column. For more information, see Oracle Virtual Columns and MySQL Generated Columns.

Summary

SELECT emp_data FROM employees;

SELECT emp_data FROM employees;

SELECT e.emp_data.address FROM employees e;

SELECT emp_data->>'address' from employees
where emp_id = 1;

SELECT e.emp_data FROM employees e
WHERE e.emp_data like '%pattern%';

SELECT e.emp_data FROM employees e
WHERE e.emp_data like '%pattern%';

SELECT e.emp_data.name FROM employees e
WHERE e.data.active = 'true';

SELECT emp_data.name FROM employees
WHERE emp_data->>"$.active" = 'true';

For more information, see The JSON Data Type and JSON Functions in the MySQL documentation.