跳至主要内容

MySQL JSON_EXTRACT() Function

The `JSON_EXTRACT()` function in MySQL is used to extract data from JSON documents. It allows you to retrieve specific values from JSON-encoded data stored in your database.

Usage

`JSON_EXTRACT()` is commonly used when you need to access or manipulate specific data within a JSON object or array. It specifies a path to the desired data, which can be a single value or a set of values.

JSON_EXTRACT(json_doc, path)

In this syntax, `json_doc` represents the JSON data, and `path` specifies the location of the desired value within the JSON document. Paths can utilize both dot notation for nested elements and bracket notation for arrays. The `$` symbol signifies the root of the JSON document.

Note: The `JSON_EXTRACT()` function is available from MySQL 5.7 onwards.

Examples

1. Basic Extraction

SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');

This example extracts the value "John" from the JSON string, accessing the `name` key.

2. Nested JSON Extraction

SELECT JSON_EXTRACT('{"user": {"name": "Jane", "age": 25}}', '$.user.age');

Here, the function retrieves the age value `25` from a nested JSON object under the `user` key.

3. Extracting from a JSON Array

SELECT JSON_EXTRACT('[10, 20, 30, {"key": "value"}]', '$[3].key');

The function extracts the value `"value"` from a JSON array element at index 3, which is a JSON object with a `key`.

Tips and Best Practices

  • Use valid JSON. Ensure that the JSON document is properly formatted and valid to avoid errors.
  • Leverage indexes. Consider indexing JSON columns for improved query performance.
  • Path syntax awareness. Be familiar with JSON path syntax, using `$` as the root and dot notation for nested elements. Arrays can also be accessed using bracket notation.
  • Handle nulls. The function will return `NULL` if the specified path does not exist in the JSON document.
  • Optimize storage. Use JSON columns instead of large text fields for better efficiency and query optimization.
  • Combine functions. Consider combining `JSON_EXTRACT()` with other SQL functions to manipulate and retrieve complex data structures.