SoFunction
Updated on 2025-04-06

MySQL database function JSON_EXTRACT sample code

Preface

In MySQL,JSON_EXTRACT()Functions are used to extract one or more values ​​from a JSON document. This function is very useful, especially when dealing with complex data stored in JSON format. Here are some things about how to useJSON_EXTRACT()Detailed description and examples of  .

Basic syntax

JSON_EXTRACT(json_doc, path [, path] ...)
  • json_doc: The JSON document to extract the value from.
  • path: One or more path expressions that specify the location of the value to be extracted. The path expression is$The beginning indicates the root of the JSON document.

Path expression

Path expressions use objects and array access syntax similar to JavaScript:

  • Object properties: point of use.Visit, for example$.name。
  • Array elements: Use square brackets[]Visit, for example$[0]。
  • Nested structure: You can use points and square brackets in combination, for example$.or$.items[0].name。

Example

Example 1: Extracting simple values

Suppose there is a tableusers, there is a fielddataStored the user's JSON data:

CREATE TABLE users (
    id INT PRIMARY KEY,
    data JSON
);

INSERT INTO users (id, data) VALUES
(1, '{"name": "Alice", "age": 30, "city": "New York"}'),
(2, '{"name": "Bob", "age": 25, "city": "Los Angeles"}');

We can useJSON_EXTRACT()Extract the name of each user:

SELECT id, JSON_EXTRACT(data, '$.name') AS name
FROM users;

Output result:

+----+--------+
| id | name   |
+----+--------+
| 1  | "Alice"|
| 2  | "Bob"  |
+----+--------+

Example 2: Extract nested values

Suppose that the JSON data contains nested objects:

INSERT INTO users (id, data) VALUES
(3, '{"name": "Charlie", "age": 35, "address": {"street": "123 Main St", "city": "Chicago"}}');

We can extract nested street addresses:

SELECT id, JSON_EXTRACT(data, '$.') AS street
FROM users;

Output result:

+----+-----------------+
| id | street          |
+----+-----------------+
| 1  | NULL            |
| 2  | NULL            |
| 3  | "123 Main St"   |
+----+-----------------+

Example 3: Extracting values ​​from array

Suppose that the JSON data contains an array:

INSERT INTO users (id, data) VALUES
(4, '{"name": "David", "age": 40, "hobbies": ["reading", "traveling", "cooking"]}');

We can extract the first hobby in the array:

SELECT id, JSON_EXTRACT(data, '$.hobbies[0]') AS hobby
FROM users;

Output result:

+----+----------+
| id | hobby    |
+----+----------+
| 1  | NULL     |
| 2  | NULL     |
| 3  | NULL     |
| 4  | "reading"|
+----+----------+

Things to note

  • JSON_EXTRACT()The result returned is a JSON value, even if it is a scalar value (such as a string or a number). If you need to convert the result to a scalar type, you can useCASTFunction. For example:

    SELECT id, CAST(JSON_EXTRACT(data, '$.name') AS CHAR) AS name
FROM users;

  • If the value pointed to by the path expression does not exist,JSON_EXTRACT()Will returnNULL。

  • The index in the path expression starts at 0.

Summarize

This is the end of this article about JSON_EXTRACT of MySQL database functions. For more related content of MySQL function JSON_EXTRACT, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!