->
This page is part of MariaDB's Documentation.
The parent of this page is: SQL Operators for MariaDB Xpand
Topics on this page:
Overview
Extract a value from JSON data using a path expression. A string result is returned with its double quotes intact.
USAGE
json_data->path
Value Name | Description |
|---|---|
| A JSON data string or JSON field |
| A path expression string |
DETAILS
The -> operator provides a result similar to calling JSON_EXTRACT() with the functions arguments separated by the operator instead of a comma.
If either the path expression or any literal JSON data is invalid, NULL is returned.
EXAMPLES
With Literal Values
SELECT '["one", "two", ["x", "y"]]'->'$[2][0]' AS result;
+--------+
| result |
+--------+
| "x" |
+--------+
SELECT '{"foo": 42, "bar": ["one", "two"]}'->'$.bar[0]' AS result;
+--------+
| result |
+--------+
| "one" |
+--------+
Error Handling
SELECT '[1, 2, 3]'->'[0]' AS result;
ERROR 3143 (HY000): [1073] Invalid JSON path expression: "at column 1: unexpected ["
An invalid JSON literal returns an error in strict mode starting in version 6.1. With strict mode disabled (or in prior versions) it returns a NULL result:
-- Disable strict mode or the select might throw an error
SET sql_mode = '';
SELECT '[1, 2, 3, foo]'->>'$[0]' AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
Per Row Extraction
CREATE TABLE t (j JSON);
INSERT INTO t VALUES
('{"id": 1, "name": {"first": "John"}}'),
('{"id": 2, "name": {"first": "Wayne"}}');
SELECT j, j->'$.id' AS id,
j->'$.name.first' AS first_name
FROM t;
+---------------------------------------+------+------------+
| j | id | first_name |
+---------------------------------------+------+------------+
| {"id": 1, "name": {"first": "John"}} | 1 | "John" |
| {"id": 2, "name": {"first": "Wayne"}} | 2 | "Wayne" |
+---------------------------------------+------+------------+