->

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

json_data

A JSON data string or JSON field

path

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.

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

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"    |
+---------------------------------------+------+------------+

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.09

  • Present starting in MariaDB Xpand 23.09.1.

6.1

  • Present starting in MariaDB Xpand 6.1.0.

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.1

  • Present starting in MariaDB Xpand 6.1.0.

EXTERNAL REFERENCES