Pop the last item in a JSON Array in MySQL 5.7

Question

I've got an array of dates in a field called from. It can look something like this.

['2016-05-01', '2016-05-03', '2016-05-04']

I want to SELECT the last item (here 2016-05-04).

I've tried this:

SELECT `from`->"$[JSON_LENGTH(`from`) - 1]" FROM `table` WHERE `id` = 3;

but got that error:

ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 2.

I've tried using a variable like this :

SET @count = (SELECT JSON_LENGTH(`from`) - 1 FROM `table` WHERE `id` = 3);
SELECT `from`->"$[@count]" FROM `table` WHERE `id` = 3;

but got the exact same error. But if I do:

SELECT `from`->"$[2]" FROM `table` WHERE `idx` = 3;

It works fine.

Answer 1

you can use :

SELECT JSON_EXTRACT(`from`,CONCAT("$[",JSON_LENGTH(`from`)-1,"]"))      FROM `table`;

to get the last item in a json array.