Brian Brian - 3 years ago 184
JSON Question

How to combine JSON_LENGTH and JSON_EXTRACT in MySQL?

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

enter image description here

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:

enter image description here

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.

Answer Source

@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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download