Bug #82311 Retrieve the last value of an array (JSON_POP)

• Submitted: 21 Jul 2016 18:43
• Modified: 21 Mar 2018 11:28
• Reporter: André Jacques
• Status: Closed
• Category: MySQL Server: JSON
• Severity: S4 (Feature request)
• Version: >5.7
• OS: Any
• CPU Architecture: Any
• Tags: JSON_POP

[21 Jul 2016 18:43] André Jacques

Description:
Let's say I've got a field name `dates` with this data :

['2016-01-01', '2016-01-04', '2016-01-05']

I would like a way to pop the last item, lets say:

SELECT JSON_POP(`dates`) FROM `table`;

+-----------------------+
| JSON_POP(`date`->"$"] |
+-----------------------+
| '2016-01-05'          |
+-----------------------+

or :

SELECT `dates`->"$[-1]" FROM `table`.

+------------------+
| `dates`->"$[-1]" |
+------------------+
| '2016-01-05'     |
+------------------+

The "-1" mechanic could be very powerful.

How to repeat:
CREATE TABLE `table` (`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `date` JSON NULL) ENGINE=InnoDB;

INSERT INTO `table` ('date') VALUES (JSON_ARRAY('2016-01-01', '2016-01-04', '2016-01-05')) ;

SELECT `date`->"$[JSON_LENGTH(`date`) - 1]" FROM `table`;
    ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 2.

SET @count = (SELECT JSON_LENGTH(`date`) - 1 FROM `table`);
SELECT `date`->"$[@count]" FROM `table`;
    ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 2.

SELECT `date`->"$[-1]" FROM `table`;
    ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 2.

[21 Mar 2018 11:28] Knut Anders Hatlen

Thanks for the enhancement request.

In MySQL 8.0 you can do the following:

mysql> select dates->'$[last]' from t;
+------------------+
| dates->'$[last]' |
+------------------+
| "2016-01-05"     |
+------------------+

This was implemented in WL#9831.