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?

onin · Accepted Answer · 2021-04-14 03:53:35Z

12

Since MySQL 8 it's much easier:

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

Other useful examples here

Note: doesn't work in MariaDB

edited Apr 14, 2021 at 3:53

onin

5,8004 gold badges43 silver badges58 bronze badges

answered Apr 3, 2021 at 23:32

sen77

1612 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

onin Over a year ago

Beat me to it by 2 hours :)

sen77 Over a year ago

Be careful it is not working in mariadb. I had to rewrite a bunch of code after migration

onin Over a year ago

Thanks for the insight. I've been thinking a lot about migrating from MySQL8 to Maria, it has a lot of features that I need. Would you recommend the migration overall, are you happier with Maria? Maria claims better performance, Is it true?

sen77 Over a year ago

I had a bug in mySQL and the only way I found to fix it was the migration to mariadb. I haven't tested it's performance before and after. I'm just happy that bug has gone.

sen77 Over a year ago

last word doesn't work in mariadb. JSON_EXTRACT/CONCAT/JSON_LENGTH work well

|

Andreas Müller · Accepted Answer · 2018-05-16 08:16:41Z

9

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

found here

edited May 16, 2018 at 8:16

answered May 16, 2018 at 7:54

Andreas Müller

1061 silver badge7 bronze badges

Collectives™ on Stack Overflow

How to get last element in a MySQL JSON array?

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related