I try to use MariaDB with JSON.
CREATE TABLE t (j LONGTEXT);
INSERT INTO t (j) VALUES ('{"a":"1"}');
INSERT INTO t (j) VALUES ('{"a":"2"}');
SELECT * FROM t WHERE JSON_VALUE(j,'$.a') = "1";
+-----------+
| j |
+-----------+
| {"a":"1"} |
+-----------+
SELECT * FROM t WHERE JSON_VALUE(j,'$.a') = "2";
+-----------+
| j |
+-----------+
| {"a":"2"} |
+-----------+
Since here it works like a charm.
But now I would not expect this.
SELECT JSON_QUERY(j, "$.a") AS a FROM t;
+------+
| a |
+------+
| NULL |
| NULL |
+------+
I expected to get sort of this.
+------+
| a |
+------+
| "1" |
| "2" |
+------+
Where I am wrong??
>Solution :
JSON_VALUE returns a scalar value, while JSON_QUERY returns an object or an array :
SELECT JSON_QUERY('{"a":"1"}', '$.a'); -- Returns NULL because "1" is a scalar value
However, if the values are objects or arrays, they will be returned as they are:
SELECT JSON_QUERY('{"key1":[1,2], "key2": {"a": 5}}', '$.key1'); -- Return [1,2]
SELECT JSON_QUERY('{"key1":[1,2], "key2": {"a": 5}}', '$.key2'); -- Return {"a": 5}