::
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 an abbreviated path expression. A string result is returned unquoted.
USAGE
json_data::abbreviated_path
Value Name | Description |
|---|---|
| A JSON data string or JSON field |
| A path expression string without an initial |
DETAILS
The :: operator provides a result similar to using the ->> operator except that the abbreviated path expression does not start with "$" or "$.". Strings results are returned unquoted, as they are with the ->> operator.
If either the abbreviated 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 |
+--------+
SELECT '{"foo": 42, "bar": ["one", "two"]}'::'bar'::'[1]' AS result;
+--------+
| result |
+--------+
| two |
+--------+
Error Handling
-- Erroneous '$':
SELECT '[1, 2, 3]'::'$[0]' AS result;
+--------+
| result |
+--------+
| NULL |
+--------+
-- Invalid JSON string value:
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 |
+---------------------------------------+------+------------+