SERIAL_EXTRACT function
Function Description
The SERIAL_EXTRACT() function is used to extract the individual elements in a sequence/tuple value and is used in conjunction with the functions MAX(), MIN(), SERIAL(), SERIAL_NULL().
Function syntax
>SERIAL_EXTRACT(serial_col, pos as type)
Parameter interpretation
| Parameters | Description |
|---|---|
| serial_col | Required parameters. Holds a string row of serial/serial_full function values. If you need to change the output type you can use it in conjunction with the CAST() function. |
| pos | Required parameters. Position of the field to extract, 0 is the first. |
| type | Required parameter. The original type of the exported element. Requires consistency with extracted element type. |
Examples
``sql <!-- validator-ignore-exec -->
drop table if exists vtab64;
create table vtab64(id int primary key auto_increment,vecf64_3vecf64(3),vecf64_5` vecf64(5));
insert into vtab64(vecf64_3,vecf64_5) values("[1,NULL,2]",NULL);
insert into vtab64(vecf64_3,vecf64_5) values(NULL,NULL);
insert into vtab64(vecf64_3,vecf64_5) values("[2,3,4]",NULL);
insert into vtab64(vecf64_3,vecf64_5) values ("[4,5,6]","[1,2,3,4,5]");
insert into vtab64(vecf64_3,vecf64_5) values ("[7,8,9]","[2,3,4,5,6]");
mysql> select * from vtab64; +------+-----------+-----------------+ | id | vecf64_3 | vecf64_5 | +------+-----------+-----------------+ | 1 | NULL | NULL | | 2 | [2, 3, 4] | NULL | | 3 | [4, 5, 6] | [1, 2, 3, 4, 5] | | 4 | [7, 8, 9] | [2, 3, 4, 5, 6] | +------+-----------+-----------------+ 4 rows in set (0.01 sec)
--max(max(serial(id, vecf64_3, vecf64_5)) gets a maximum serialized value, and then normally the max obtained would be the record (4, [7, 8, 9], [2, 3, 4, 5, 6]), but the 1 represents the value in the second position, so that's [7, 8, 9].
mysql> select serial_extract(max(serial(id, vecf64_3, vecf64_5)), 1 as vecf64(3)) as a from vtab64;
+-----------+
| a |
+-----------+
| [7, 8, 9] |
+-----------+
1 row in set (0.01 sec)
mysql> select serial_extract(min(serial(id, vecf64_3, vecf64_5)), 2 as vecf64(5)) as a from vtab64;
+-----------------+
| a |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+
1 row in set (0.00 sec)
mysql> select serial_extract(max(serial_full(cast(id as decimal), vecf64_3)), 0 as decimal) as a from vtab64;
+------+
| a |
+------+
| 4 |
+------+
1 row in set (0.01 sec)
mysql> select serial_extract(min(serial_full(cast(id as decimal), vecf64_3)), 1 as vecf64(3)) as a from vtab64;
+------+
| a |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
```