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.

I've tried `SELECT \`from\`->"$[-1]" FROM \`table\` WHERE \`idx\` = 3;` with the same error... — Havarem, Jul 21 '16 at 18:54

jmounim · Answer 1 · 2017-03-07T00:11:26.800

9

you can use :

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

to get the last item in a json array.

edited Mar 07 '17 at 00:11

answered Mar 07 '17 at 00:02

jmounim

do you know to keep all the elements except last one :) – ice6 Sep 23 '19 at 02:26
@ice6 added an answer that solve the op question and your question in newer versions of MySQL – Brian Leishman Sep 09 '22 at 19:54

score 0 · Answer 2 · answered Sep 09 '22 at 19:52

MySQL 8 brings a very straight forward way to accomplish this:

select json_extract(json_array(1, 2, 3, 4, 5), '$[last]');

which returns

Can also do cool ranges, like everything but the last one:

select json_extract(json_array(1, 2, 3, 4, 5), '$[0 to last-1]');

which returns

[1, 2, 3, 4]

2 Answers2