How to combine JSON_LENGTH and JSON_EXTRACT in MySQL?

Question

Let's say I have a MySQL table like this:

This table can be created by

CREATE TABLE `MyTable` (

`JsonData` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,

`ExtractedData` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL

);



INSERT INTO MyTable (JsonData, ExtractedData)

VALUES ('[10, 20, 30]', NULL);



INSERT INTO MyTable (JsonData, ExtractedData)

VALUES ('["a", "b"]', NULL);



INSERT INTO MyTable (JsonData, ExtractedData)

VALUES ('["q", "w", "e", "r"]', NULL);

How can I copy the second last element of json data to

ExtractedData

?

I mean I want to update the table so that it looks like this:

I've found that

select @len := JSON_LENGTH(JsonData) from MyTable;

is able to get the length of every

JsonData

.

I've also found that

UPDATE MyTable SET ExtractedData = JSON_EXTRACT(JsonData, '$[0]');

is able to copy the first element of

JsonData

to

ExtractedData

.

How can I combine the 2 commands so that I can copy the second last element to

ExtracedData

?

UPDATE MyTable SET ExtractedData = JSON_EXTRACT(JsonData, '$[@len-2]');

doesn't work.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

@len here is a table. You can't automap it to each update row.

You need to inline the length, e.g.

UPDATE MyTable SET ExtractedData = JSON_EXTRACT(JsonData, CONCAT('$[',JSON_LENGTH(JsonData)-1,']'));

Check the fiddle at https://www.db-fiddle.com/f/6PC2CaBxwDdfpruECqAjGR/1

How to combine JSON_LENGTH and JSON_EXTRACT in MySQL?

Sign up