MySQL JSON Data Type: Storage and Query Functions

This article provides a comprehensive overview of how MySQL stores JSON documents and the essential functions available to query them. You will learn about the internal binary format MySQL uses for optimized performance, followed by a practical guide to the most common JSON functions and operators used to extract, search, and modify JSON data directly within your SQL queries.

How MySQL Stores JSON Documents

MySQL introduced a native JSON data type in version 5.7, moving away from storing JSON as raw text (like VARCHAR or TEXT). When you insert a JSON document into a MySQL table, the database server does not store it as a plain string. Instead, it performs two critical operations:

1. Automatic Validation: MySQL parses the document to ensure it is valid JSON according to RFC 7159. If the document is invalid, the insert or update operation fails with an error.
2. Binary Format Serialization: MySQL converts the validated JSON document into an internal binary format. This binary representation is optimized for quick read access. It structures the document as a tree, allowing the database engine to search for and extract nested elements, keys, and values without parsing the entire document from scratch.

By storing JSON in this binary format, MySQL avoids the overhead of text-parsing during read operations, significantly improving query performance.

Querying JSON Documents in MySQL

MySQL provides a rich set of built-in functions and path operators to query, extract, and search JSON data. These operations utilize a path syntax, where $ represents the root of the JSON document.

1. Extracting Data

To retrieve specific values from a JSON column, you can use either functions or shorthand operators.

• JSON_EXTRACT(json_doc, path): Returns data from a JSON document at the specified path.

  SELECT JSON_EXTRACT(info, '$.user.name') FROM profiles;

• The Column-Path Operator (->): An alias for JSON_EXTRACT.

  SELECT info->'$.user.name' FROM profiles;

• The Inline Path Operator (->>): This operator extracts the value and unquotes the result, which is ideal when you need to output clean string values. It is equivalent to calling JSON_UNQUOTE(JSON_EXTRACT(...)).

  SELECT info->>'$.user.name' FROM profiles;

2. Searching and Validating JSON

You can filter rows based on the contents or structure of your JSON documents using search functions:

• JSON_CONTAINS(target, candidate[, path]): Checks if a target JSON document contains a specific candidate value. It returns 1 (true) or 0 (false).

  -- Find rows where the "tags" array contains the value "admin"
  SELECT * FROM profiles WHERE JSON_CONTAINS(info->'$.tags', '"admin"');

• JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path]...): Checks if a JSON document contains data at the specified path or paths. The one_or_all argument dictates whether only one path ('one') or all specified paths ('all') must exist.

  SELECT * FROM profiles WHERE JSON_CONTAINS_PATH(info, 'one', '$.user.phone', '$.user.email');

• JSON_KEYS(json_doc[, path]): Returns the keys of the JSON object as a JSON array.

  SELECT JSON_KEYS(info->'$.user') FROM profiles;

3. Modifying JSON Data

While querying is highly optimized, you can also update specific parts of a JSON document without rewriting the entire object using modification functions:

• JSON_SET(json_doc, path, val[, path, val]...): Inserts or updates data. It replaces existing values and adds non-existing values.
• JSON_INSERT(json_doc, path, val[, path, val]...): Inserts data only if the path does not already exist.
• JSON_REPLACE(json_doc, path, val[, path, val]...): Replaces existing values only.

-- Example of updating a user's status within the JSON column
UPDATE profiles 
SET info = JSON_SET(info, '$.user.status', 'active') 
WHERE id = 1;