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}