Location>code7788 >text

MySQL's JSON query

Popularity：560 ℃/2025-03-27 16:52:40

MySQL's JSON path format

MySQL uses specific JSON path expression syntax to navigate and extract data from JSON documents

Basic structure

The JSON path in MySQL follows the following common format

$[Path Component]

Detailed explanation of path components

| Operator | Description | Example |
 | ----------- | --------- | --------------------- |
 | $ \| Root object \| $ |
 | . or [] | Member access | $.name or $['name'] |
 | [*] | Array Wildcards | $.items[*] |
 | [n] | Array Index | $[0] |
 | [m to n] | Array range | $[1 to 3] |
 | ** | Recursive wildcard | $**.price |

1. Root object ($)

  • $Represents the entire JSON document

2. Member visits (.or[])

  • Point notation:$.
  • Bracket notation:$['store']['book']
  • Use bracket notation when the key name contains special characters or spaces

3. Array access

  • All elements:$[*]or$.array[*]
  • Specify the index:$[0]Count starts at 0
  • scope:$[1 to 3]（MySQL 8.0.26+）

4. Wildcard

  • *Match all members/elements at the current level
  • **Recursively searching all paths (MySQL 8.0.26+)

Special syntax elements

1. Filter expressions (MySQL 8.0.4+)

$.items[?(@.price > 10)]
  • ?Introduce filtering expressions
  • @Represents the current element

2. Path range (MySQL 8.0.26+)

$[1 to 3] // Element 1 to 3
 $[last-1] // The penultimate element
 $[last-2 to last] // The last three elements

Actual examples

Simple path

-- Extract scalar values
 SELECT JSON_EXTRACT('{"name": "Zhang San", "age": 30}', '$.name');

 -- Array element, output "b", note that it is in double quotes
 SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1]');

Complex paths

--Nested Objects
 SELECT JSON_EXTRACT('{"store": {"book": {"title": "MySQL Guide"}}}', '$.');

 -- Object array
 SELECT JSON_EXTRACT('{"items": [{"id": 1}, {"id": 2}]}', '$.items[*].id');

Filter expressions

-- Find high-priced products
 SELECT JSON_EXTRACT('{"items": [{"id": 1, "price": 5}, {"id": 2, "price": 15}]}',
        '$..items[?(@.price > 10)].id');

Abbreviation operator

MySQL provides abbreviation for common operations

  • ->: equivalent toJSON_EXTRACT()
  • ->>: equivalent toJSON_UNQUOTE(JSON_EXTRACT()) 
--The following two writing methods are equivalent:
 SELECT json_column->'$.name';
 SELECT JSON_EXTRACT(json_column, '$.name');

 -- The following two writing methods are equivalent (returning a string with quotes removed):
 SELECT json_column->>'$.name';
 SELECT JSON_UNQUOTE(JSON_EXTRACT(json_column, '$.name'));

Notice

  1. Path expressions are case sensitive
  2. Returns NULL if the non-existent path (no error will be reported)
  3. **Recursive operators may affect performance
  4. Filter expressions support comparison operators:=、!=、<、>wait

MySQL's JSON_TABLE function

All you know that the result obtained in this way is not a real row-column structure. The JSON_TABLE function introduced in MySQL 8.0 can convert JSON data into a relational table format and convert each element in the array into a row of data in the table.

Functions of JSON_TABLE

  1. Expand the JSON array into multi-line records
  2. Extract nested JSON object properties
  3. Convert semi-structured data into structured data

JSON_TABLE Usage

JSON_TABLE(
     json_doc, -- field or value of type JSON
     path_expression -- JSON path expression
     COLUMNS( -- Column definition for new table
         column_name column_type PATH json_path [on_empty] [on_error],
         ...
     )
 ) [AS] alias

Parameter description

  1. json_doc: Can be a JSON string literal, or a JSON type column in a table
  2. path_expression: The path to the JSON array to be expanded
  3. COLUMNS: Define the structure of the output column
    • column_name: The generated column name
    • column_type: Data types (such as VARCHAR, INT, JSON, etc.)
    • PATH: Specify the data extraction path
  4. alias: The table alias that must be provided

Actual cases

Expand an array of integers into one column and multiple rows

SELECT *
FROM JSON_TABLE(
    '[1, 2, 3]',
    '$[*]' COLUMNS(
        rowid FOR ORDINALITY,
        value INT PATH '$'
    )
) AS t;

Output

rowid | value
------+-------
1     | 1
2     | 2
3     | 3

Expand an array of objects into multiple columns and rows

SELECT *
 FROM JSON_TABLE(
     '[{"name":"Zhang San","age":25},{"name":"Li Si","age":30}]',
     '$[*]' COLUMNS(
         name VARCHAR(20) PATH '$.name',
         age INT PATH '$.age',
         adult VARCHAR(3) PATH '$.age' DEFAULT 'No' ON EMPTY
     )
 ) AS t;

Output

name | age | adult
 -----+-----+--------------------------------------------------------------------------------------------------------------------
 Zhang San | 25 | No
 Li Si | 30 | No

Expand in the data table

If JSON is a field in the table, you can usetable_1 CROSS JOIN JSON_TABLE(...)Expand, for example, the field of a table v_video is a JSON field. You need to expand a member sequences in the result, and write it as SQL as follows

SELECT 
    ,
    e.match_id,
    ->>'$.id' AS json_id,
    ->>'$.sf' AS sf_value,
    ->>'$.ef' AS ef_value,
    ->>'$.ef' - ->>'$.sf'AS duration
FROM 
    v_video e
        CROSS JOIN JSON_TABLE(
            ->'$.sequences',
            '$[*]' COLUMNS (
                tag JSON PATH '$'
            )
        ) AS j ON e.match_id = 294

The above SQL is expanded through CROSS JOIN JSON_TABLE to expand the sequences array under each row field. Each array element becomes a new field tag. At this time, it is still a JSON, and then passes in SELECT.->>Extract the values ​​in it and get a completely expanded new table.

Advanced Usage

FOR ORDINALITY clause

Generate self-increased row number columns

COLUMNS(
    id FOR ORDINALITY,
    ...
)

Nested path processing

COLUMNS(
    NESTED PATH '$.nested_obj' COLUMNS(
        sub_col1 INT PATH '$.prop1',
        sub_col2 VARCHAR(10) PATH '$.prop2'
    )
)

The above example can be rewritten as

SELECT 
    ,
    ,
    ,
     -  AS duration
FROM 
    v_video e
CROSS JOIN 
    JSON_TABLE(
        ->'$.sequences',
        '$[*]' COLUMNS (
            id FOR ORDINALITY,
            NESTED PATH '$' COLUMNS(
                ef INT PATH '$.ef',
                sf INT PATH '$.sf'
            )
        )
    ) AS j ON e.match_id = 294

The above SQL, byNESTED PATH ... COLUMNS(...)Further expand a JSON element in the expanded array into multiple fields.

Error handling

COLUMNS(
    ef INT PATH '$.ef' NULL ON EMPTY NULL ON ERROR,
    sf INT PATH '$.sf' DEFAULT '0' ON EMPTY NULL ON ERROR
)

The format is

on_empty:
    {NULL | DEFAULT json_string | ERROR} ON EMPTY

on_error:
    {NULL | DEFAULT json_string | ERROR} ON ERROR

Things to note

  1. MySQL version is higher than 8.0
  2. The path expression must point to the JSON array, note thatArray
  3. Aliases must be specified for the result set
  4. Can be used in both the FROM clause and the JOIN clause
  5. In terms of performance, using JSON_TABLE for large data sets may be slow, creating function indexes for JSON columns can improve query performance
<Previous Next>