Extract JSON Value from Table

Question

I have a MariaDB table called genres with these columns (genres is a JSON column):

+----+------------------------------------+
| id | genres                             |
+----+------------------------------------+
| 1  | ["Action", "Martial Arts", "Love"] |
+----+------------------------------------+

If I query:

SELECT JSON_EXTRACT( (SELECT genres.genres FROM genres), '$[2]' );

I get:

"Love"

Which is correct. But if I try to use the JSON_EXTRACT() shortcut arrow operator, -> :

SELECT genres.genres->"$[2]" FROM genres;

I get:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>"$[1]" FROM genres' at line 1

Why can't I use the arrow operator? Is something wrong with my syntax or data? Why does it only work when I do it the long way, with JSON_EXTRACT()?

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_json-column-path Does it work in MariaDB too? — GTS Joe, Mar 23 '20 at 08:59
Why not? It has many examples of using the JSON -> operator. — GTS Joe, Mar 23 '20 at 09:14

score 3 · Accepted Answer · answered Mar 23 '20 at 11:44

3

column -> path and column ->> path operators are not supported in MariaDB-10.2, 10.3 even in 10.4 yet.

This might be shorter version for your case

SELECT JSON_EXTRACT( genres , '$[2]' ) FROM genres

answered Mar 23 '20 at 11:44

Barbaros Özhan

59,113
10
31
55

2

Great answer, thanks for suggesting the shorter version. It'll do until MariaDB supports -> and ->>. – GTS Joe Mar 23 '20 at 16:27
https://jira.mariadb.org/browse/MDEV-13594 https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/#incompatibilities-between-currently-unsupported-mariadb-versions-and-mysql Aaa not priority for themm.. – Benyamin Limanto Dec 26 '22 at 07:42

Extract JSON Value from Table

1 Answers1