SERIAL_EXTRACT function
Function description
The SERIAL_EXTRACT() function is used to extract each element in a sequence/tuple value. It needs to be used in combination with functions MAX(), MIN(), SERIAL(), SERIAL_NULL().
Function Syntax
>SERIAL_EXTRACT(serial_col, pos as type)
Parameter definition
| Parameters | Description |
|---|---|
| serial_col | Required parameters. Save serial columns 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. The position of the field to be extracted is 0 as the first. |
| type | Required parameters. The original type of the exported element. Need to be consistent with the extracted element type. |
Example
drop table if exists vtab64;
create table vtab64(id int primary key auto_increment,`vecf64_3` vecf64(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`)) Get the largest serialized value, and the max you get normally should be (4,[7, 8, 9],[2, 3, 4, 5, 6]) record, but 1 represents the value of the second position, so it is [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)