Search code examples
jsonmariadbmariadb-10.2

Extract JSON Value from Table

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()?

Solution

  • 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