7

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?

Geek Stocks
  • 2,010
  • 3
  • 27
  • 43

2 Answers2

9

Since MySQL 8 it's much easier:

`json_field`->> '$[last]'

Other useful examples here

Note: doesn't work in MariaDB

The Onin
  • 5,068
  • 2
  • 38
  • 55
sen77
  • 131
  • 2
  • 5
8
SELECT JSON_EXTRACT(`json_field`,CONCAT("$.my_array[",JSON_LENGTH(`json_field` ->> '$.my_array')-1,"]")) from `my_table` where `id` = 1;

found here

Andreas Müller
  • 96
  • 1
  • 7