MySQL JSON

Section 1: Introduction to JSON in MySQL

This section introduces you to the JSON data type in MySQL and a brief overview of some useful JSON functions.

• JSON Data Type – Introduction to JSON data type in MySQL.
• JSON_ARRAY() – Create JSON arrays from a list of values.
• JSON_OBJECT() – Create JSON objects from key-pair values.

Section 2: Searching in JSON documents

In this section, you’ll learn how to construct JSON path expressions and use them to search for data in JSON documents.

• JSON Path – Show you how to construct JSON path expressions to navigate through elements or specify a piece of data within a JSON document.
• JSON_CONTAINS_PATH() – Check whether a JSON document contains specified paths.
• JSON_CONTAINS() – Show you how to check if a JSON document contains another JSON document at a specified path.
• JSON_OVERLAPS() – Compare two JSON documents and return true if the two documents have key-value pairs or array elements in common.
• JSON_SEARCH() – Return a path that matches a given string within a JSON document.
• JSON_KEYS() – Learn how to get the keys specified by a path in a JSON document.

Section 3: Modifying JSON Documents

This section shows you how to modify a JSON document by inserting values, replacing existing values with new ones, or adding non-existing values to a JSON document.

• JSON_INSERT() – Insert one or more values into a JSON document without replacing existing values.
• JSON_REPLACE() – Replace only existing values in a JSON document with new values.
• JSON_SET() –  Replace existing values and add non-existing values to a JSON document.
• JSON_REMOVE() – Remove elements from a JSON document.
• JSON_MERGE_PATCH() – Merge two or more JSON documents into a single JSON document while discarding duplicate keys.
• JSON_MERGE_PRESERVE() – Merge two or more JSON documents into a single JSON document while preserving the structure of the original JSON document.

Section 4: Querying JSON Documents

• JSON_EXTRACT() – Retrieve JSON data using JSON_EXTRACT and JSON_UNQUOTE functions as well as the -> and ->> operators.
• JSON_VALUE() – Extract a value at a specific path from a JSON document and optionally convert it to a value of a desired type.

Section 5. Working with JSON arrays

In this section, you’ll learn about functions that handle JSON arrays including creating JSON arrays, appending an element to an array, and inserting elements into an array.

• JSON_ARRAY_APPEND() – Append one or more elements at the end of a JSON array within a JSON document.
• JSON_ARRAY_INSERT() – Insert one or more elements into a specified position in a JSON array within a JSON document.
• MEMBER OF – Determine if a value is a member of a JSON array.

Section 6: Aggregating JSON Data

This section introduces to you two aggregate functions that aggregate data into JSON arrays and JSON objects.

• JSON_ARRAYAGG() – aggregate values into a JSON array.
• JSON_OBJECTAGG() – aggregate key-value pairs from columns into a JSON object.

Section 7: Indexing JSON data

• Indexing JSON – show you how to use functional indexes to index JSON data for faster JSON retrieval.

Section 8: Getting attributes of JSON values

This section introduces you to functions that get the attributes of JSON values.

• JSON_TYPE() – Get the type of a JSON value.
• JSON_LENGTH() – Get the length of a JSON document or a value identified by a path within a JSON document.
• JSON_DEPTH() – Get the depth of a JSON document.

Section 9: JSON table functions

This section introduces you to functions that convert JSON data to tabular data.

• JSON_TABLE() – Convert JSON documents to tabular data.

Section 10: JSON validation functions

This section introduces you to some of the JSON utility functions.

• JSON_VALID() – Check whether a value is a valid JSON document.
• JSON_SCHEMA_VALID() – Validate a JSON document based on a JSON schema.
• JSON_SCHEMA_VALIDATION_REPORT – Validate a JSON document based on a JSON schema and report the reason for the validation violations.

Section 11: JSON utility functions

This section introduces you to some of the JSON utility functions.

• JSON_PRETTY() – Pretty-print JSON data for better readability.
• JSON_STORAGE_SIZE() – Obtain the storage size in bytes of JSON data.
• JSON_STORAGE_FREE() –  Get how much space was freed after it was updated for a JSON column value.
• JSON_QUOTE() – Quote a string as a JSON value by wrapping it with double quote characters and escaping interior quotes and other characters.
• JSON_UNQUOTE() – Remove double quotes from a JSON value.