MySQL JSON Path

Summary: in this tutorial, you will learn about MySQL JSON path and how to use it to specify and navigate through elements in a JSON document.

What is JSON path?

JSON path is a way to specify and navigate through the elements in a JSON document. It serves as a roadmap to locate specific pieces of data within a JSON structure.

In MySQL, you can use JSON path expressions to locate elements within a JSON document.

MySQL JSON path syntax elements

The following outlines the key elements of JSON path syntax:

Scope ($): The scope represents the JSON document, which serves as its root.

Path Legs: A path may consist of one or more path legs. There are three types of path legs:

• Member: Using a period (.) followed by a key name or asterisk (*). The member is used to access a member of an object.
• Array Location: Using square brackets ([]) to access array elements by their indexes (N) or using an asterisk to access all elements in an array.
• Double Asterisk (**): This is a special token that represents a recursive search for all paths within the JSON document.

Key names: Key names are enclosed in double quotes or can be valid ECMAScript identifiers, specifying the names of keys in a JSON object.

MySQL JSON path examples

Let’s explore some examples of using the JSON path expressions to specify elements in a JSON document.

1) Accessing a specific key in an object

Suppose you have a JSON document that represents information about a person:

{
   "name": "John",
   "age": 25,
   "job": "MySQL Developer"
}Code language: SQL (Structured Query Language) (sql)

To access the age key, you can use the following path expression:

"$.age"Code language: SQL (Structured Query Language) (sql)

In this expression, the $ represents the root of the document. The period (.) allows you to access a member of a JSON object. The age is the name of the member of the object.

The following uses the JSON_EXTRACT() function to extract the age value from the above JSON object:

SELECT 
  JSON_EXTRACT(
    '{"name":"John","age":25,"job":"MySQL Developer"}', 
    "$.age"
  ) ageCode language: SQL (Structured Query Language) (sql)

Output:

25Code language: SQL (Structured Query Language) (sql)

The query returns 25 as expected.

2) Accessing all members in an object

The following path accesses all members of the object:

'$.*'Code language: SQL (Structured Query Language) (sql)

In this path:

• The $ represents the root of the JSON document
• The period (.) allows access to one or more members of an object.
• The * means all members of an object.

Here’s the query that uses the path "$.*" to extract all values of the members of a JSON object:

SELECT 
  JSON_EXTRACT(
    '{"name":"John","age":25,"job":"MySQL Developer"}', 
    "$.*"
  ) properties;Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------------------+
| properties                      |
+---------------------------------+
| [25, "MySQL Developer", "John"] |
+---------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The JSON_EXTRACT() function returns a JSON array containing the values of all members of the JSON object.

3) Accessing an array element by its index

Suppose you have the following JSON array:

["PHP", "MySQL", "JavaScript"]Code language: SQL (Structured Query Language) (sql)

To access the second element of the array, you use the path expression:

'$[1]'Code language: SQL (Structured Query Language) (sql)

The following example uses the JSON_EXTRACT() method to retrieve the second element of the array:

SELECT 
  JSON_EXTRACT(
    '["PHP","MySQL","JavaScript"]', 
    "$[1]"
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+---------+
| result  |
+---------+
| "MySQL" |
+---------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

4) Accessing all elements in an array

To access all elements in a JSON array, you use the path expression:

'$[*]'Code language: SQL (Structured Query Language) (sql)

In this path expression:

• $ represents the root of the JSON document.
• [] accesses array elements.
• * means all elements.

The following example uses the JSON_EXTRACT() function to get all elements of the array:

SELECT 
  JSON_EXTRACT(
    '["PHP","MySQL","JavaScript"]', 
    "$[*]"
  ) result;Code language: SQL (Structured Query Language) (sql)

It returns a JSON array that contains all elements of the JSON array in the original JSON document:

+--------------------------------+
| result                         |
+--------------------------------+
| ["PHP", "MySQL", "JavaScript"] |
+--------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

5) Combining path legs

Suppose you have the following JSON document:

{
  "name": "John",
  "age": 25,
  "job": "MySQL Developer",
  "skills": [
    "PHP",
    "MySQL",
    "JavaScript"
  ]
}Code language: SQL (Structured Query Language) (sql)

The following path expression accesses the second element of the skills array in the JSON document:

$.skills[1]Code language: SQL (Structured Query Language) (sql)

Here’s the query that extracts the second element of the skills array within the JSON document:

SELECT 
  JSON_EXTRACT(
    '{"name":"John","age":25,"job":"MySQL Developer","skills":["PHP","MySQL","JavaScript"]}', 
    "$.skills[1]"
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+---------+
| result  |
+---------+
| "MySQL" |
+---------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use JSON path to navigate through the elements in a JSON document.
• Use the dollar ($) as the current JSON document.
• Use the period (.) to access a member of an object.
• Use the [N] to access the (N-1)^th element of an array.
• Use the asterisk (*) to access all members of an object or all elements of an array.