How to get last element in a MySQL JSON array?

Question

I recently found time to upgrade to MySQL 5.7 and I am testing the new JSON functions. So far, pretty awesome!

I have a use case where I need to access the last element in a JSON array. Its easy to retrieve an element when you know the ordinal like this:

SELECT `json_field`->"$.my_array[0]" from `my_table` where `id` = 1;

But in the case when you don't know how many elements you have, this is not available to you. Now, you can find out (and store) how many elements there are like this:

set @arrayLength = (SELECT JSON_LENGTH(`json_field`->"$.my_array") from `my_table` where `id` = 1);

But when you go to use the variable, you do not get a result.

SELECT `json_field`->"$.my_array[@arrayLength - 1]" from `my_table` where `id` = 1;

Has anyone solved a similar problem with MySQL 5.7 yet?

Answer 1

SELECT JSON_EXTRACT(`json_field`,CONCAT("$.my_array[",JSON_LENGTH(`json_field` ->> '$.my_array')-1,"]")) from `my_table` where `id` = 1;

found here