JSON_EXTRACT()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
Returns extracted data from some JSON data by applying a path selector.
USAGE
JSON_EXTRACT(json_data, path[, path ...])
Argument Name | Description |
|---|---|
| The JSON data to query |
| The path in the data where the query is targeted |
DETAILS
JSON_EXTRACT() is a JSON query function that returns data at the queried JSON paths from valid JSON data.
JSON data is valid if it conforms to ECMA-404: The JSON Data Interchange Standard.
If multiple paths are supplied in a function call, the result is returned as a JSON array.
When a string value is extracted, it will be inside double quotes. See JSON_
A NULL is returned if any of the arguments are NULL or invalid.
EXAMPLES
With Literal Values
In the following example, JSON_EXTRACT() is called with a JSON literal value:
SELECT JSON_EXTRACT('{"list": [1, 2, 3],
"obj": {"one": 1, "two": 2, "three": 3}}',
'$.obj', '$.list'
) AS result;
+-----------------------------------------------+
| result |
+-----------------------------------------------+
| [{"one": 1, "two": 2, "three": 3}, [1, 2, 3]] |
+-----------------------------------------------+
SELECT JSON_EXTRACT('[1]', NULL, '$[0]') AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
Example Schema and Data
Some of the examples are based on the contacts table:
CREATE TABLE contacts (
contact JSON
);
INSERT INTO contacts VALUES
('{"id": 1, "email": "john.smith@example.com", "name1": "John", "name2": "Smith", "is_new": [false]}'),
('{"id": 2, "email": "jon.smith@example.com", "name1": "Jon", "name2": "Smith", "is_new": [false]}'),
('{"id": 3, "email": "johnny.smith@example.com", "name1": "Johnny", "name2": "Smith"}');
Per-row Extraction
This example finds several items in each row's JSON data:
SELECT contact,
JSON_EXTRACT(contact,'$.id') AS ex_id,
JSON_EXTRACT(contact,'$.name1') AS ex_name1,
JSON_EXTRACT(contact,'$.name2') AS ex_name2,
JSON_EXTRACT(contact,'$.email') AS ex_email,
JSON_EXTRACT(contact,'$.is_new') AS ex_is_new
FROM contacts\G
*************************** 1. row ***************************
contact: {"id": 1, "email": "john.smith@example.com", "name1": "John", "name2": "Smith", "is_new": [false]}
ex_id: 1
ex_name1: "John"
ex_name2: "Smith"
ex_email: "john.smith@example.com"
ex_is_new: [false]
*************************** 2. row ***************************
contact: {"id": 2, "email": "jon.smith@example.com", "name1": "Jon", "name2": "Smith", "is_new": [false]}
ex_id: 2
ex_name1: "Jon"
ex_name2: "Smith"
ex_email: "jon.smith@example.com"
ex_is_new: [false]
*************************** 3. row ***************************
contact: {"id": 3, "email": "johnny.smith@example.com", "name1": "Johnny", "name2": "Smith"}
ex_id: 3
ex_name1: "Johnny"
ex_name2: "Smith"
ex_email: "johnny.smith@example.com"
ex_is_new: NULL
With Multiple Paths
This example queries the same data as the one above but it returns them in a single JSON array:
SELECT contact,
JSON_EXTRACT(contact,
'$.id', '$.name1', '$.name2', '$.email', '$.is_new'
) as obj_order,
JSON_ARRAY(
JSON_EXTRACT(contact, '$.id'),
JSON_EXTRACT(contact, '$.name1'),
JSON_EXTRACT(contact, '$.name2'),
JSON_EXTRACT(contact, '$.email'),
JSON_EXTRACT(contact, '$.is_new')
) as arg_order
FROM contacts\G
*************************** 1. row ***************************
contact: {"id": 1, "email": "john.smith@example.com", "name1": "John", "name2": "Smith", "is_new": [false]}
obj_order: [1, "John", "Smith", "john.smith@example.com", [false]]
arg_order: [1, "John", "Smith", "john.smith@example.com", [false]]
*************************** 2. row ***************************
contact: {"id": 2, "email": "jon.smith@example.com", "name1": "Jon", "name2": "Smith", "is_new": [false]}
obj_order: [2, "Jon", "Smith", "jon.smith@example.com", [false]]
arg_order: [2, "Jon", "Smith", "jon.smith@example.com", [false]]
*************************** 3. row ***************************
contact: {"id": 3, "email": "johnny.smith@example.com", "name1": "Johnny", "name2": "Smith"}
obj_order: [3, "Johnny", "Smith", "johnny.smith@example.com"]
arg_order: [3, "Johnny", "Smith", "johnny.smith@example.com", null]