->>
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 unquoted.
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. The resulting value is also unquoted, similar to calling JSON_UNQUOTE().
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 |
+---------------------------------------+------+------------+