mysql> insert into x(y) values('["a","b","c","d"]');
Query OK, 1 row affected (0.10 sec)
You could get all the values from that array using $[*]
mysql> select y->"$[*]" from x; +----------------------+ | y->"$[*]" | +----------------------+ | ["a", "b", "c", "d"] | +----------------------+ 1 row in set (0.00 sec)Or the individual members of the array with an index that starts with zero.
mysql> select y->"$[0]" from x; +-----------+ | y->"$[0]" | +-----------+ | "a" | +-----------+ 1 row in set (0.00 sec)
But what about the times you want the last item in the array and really do not want to loop through all the items? How about using 'last'?
mysql> select y->"$[last]" as 'last' from x; +------+ | last | +------+ | "d" | +------+ 1 row in set (0.00 sec)
Holey Moley! What is this? Well Roland Bouman, who was my predecessor on the MySQL Certification Team many years ago, still contributes to MySQL on a regular basis. He put in a (feature request for a JSON range operator.
So please thank Roland when you see him!!
Be sides 'last' there is 'to' too!.
mysql> select y->'$[last - 3 to last - 1]' as 'last three' from x; +-----------------+ | last three | +-----------------+ | ["a", "b", "c"] | +-----------------+ 1 row in set (0.01 sec)You can also use the ordinal number, here $[1], to strip off the first value of the array from the rest.
mysql> select y->'$[1 to last]' as 'strip first' from x; +-----------------+ | strip first | +-----------------+ | ["b", "c", "d"] | +-----------------+ 1 row in set (0.00 sec)
This makes juggling array data in JSON columns much easier.
