Vector Collection Functions
Zen vector collection functions enable the creating and dropping of collections, the inserting, updating, and deleting of data points in collections, and searching based on vector data characteristics. Points are defined as individual data units within a collection. Each point has a unique ID and contains a vector with optional metadata.
The following table lists Zen vector collection functions by their purpose.
Purpose
Function Description
Collection Operations
dbo.vector_collection_create
Creates a collection.
dbo.vector_collection_drop
Removes a collection from the database.
Point Operations
dbo.vector_point_upsert
Inserts or updates a specific point in a collection.
dbo.vector_point_upsert_batch
Inserts or updates multiple points in a collection.
dbo.vector_point_delete
Deletes a specific point in a collection.
dbo.vector_point_delete_batch
Deletes multiple points in a collection.
Retrieval Operations
dbo.vector_collection_search
Used in a FROM clause to query vector data.
Return Status
A vector collection function returns one of the following status values depending on execution results:
• SQL_SUCCESS
• SQL_STILL_EXECUTING
• SQL_ERROR
Notes about Vector Collection Functions
The following things are true about the functions:
• Function arguments can be only in the form of constants or dynamic parameters.
• The functions support stored procedures.
• Dbo.vector_collection_search returns a result set, works in the same manner as views, and can be referenced only in the FROM clause of a SELECT statement.
• Only dbo.vector_collection_search is supported in user-defined functions.
• SQL Editor in ZenCC can execute vector collection functions. However, collections do not use SQL metadata, so no DDFs are created and the collections do not appear in Zen Explorer.
Note:  Most popular SQL editors do not use statement delimiters to execute multiple statements. However, SQL Editor in ZenCC requires them. If you wish to execute the examples in other environments, you may need to remove the semicolon or pound sign separators.
About the Examples
The examples provided with each function can be executed in ZenCC in the order in which the functions are documented here. As you run the examples, you can use the following statement to see the progression of results:
SELECT a.U64_ID, a.UUID, Similarity_Score, Vector_Data, Payload
FROM dbo.vector_collection_search ('my_collection', null, '[0,0,0]', 9999, 3 ) a;
dbo.vector_collection_create
This function creates a collection.
Syntax
dbo.vector_collection_create ( collection_name, collection_config )
Arguments
Parameter
Type
Description
collection_name
STRING
Name of the collection.
collection_config
JSON object
An object containing the dimension of vectors in the collection and the type of distance_metric for vector comparisons. Valid distance_metric values are cosine, dot, and euclidean.
Results
The new collection appears by default in C:\ProgramData\Actian\Zen\collections on Windows and /usr/local/actianzen/vde_data/collections on Linux.
Example
This example creates a collection of dimension 3 and a cosine distance metric for its vectors.
dbo.vector_collection_create ( 'my_collection',
'{
"dimension" : 3,
"distance_metric" : "cosine"
}' );
dbo.vector_collection_drop
This function drops a collection.
Syntax
dbo.vector_collection_drop ( collection_name )
Arguments
Parameter
Type
Description
collection_name
STRING
Name of the collection to drop.
Results
The collection is removed from C:\ProgramData\Actian\Zen\collections on Windows and /usr/local/actianzen/vde_data/collections on Linux.
Example
This example drops a collection.
dbo.vector_collection_drop ( 'my_collection' );
dbo.vector_point_upsert
This function inserts or updates a single point in a collection.
Note:  A point is identified by its unique identifier, which can be either an 64-bit unsigned integer u64_id or a hexadecimal representation of a 128-bit UUID. Typically, a collection uses only one type of ID, rather than mixing IDs in the same collection. If valid IDs are present as both u64_id and UUID values, UUID takes precedence
Syntax
dbo.vector_point_upsert ( collection_name, point_id, vector_data, payloads )
Arguments
Parameter
Type
Description
collection_name
STRING
Name of the target collection
point_id
JSON object
An object containing a unique identifier and a dimension for the vector data.
vector_data
JSON array
An array of floats. Array size must equal the dimension.
payloads
JSON object
An object containing payload/metadata information.
Examples
This example inserts a new point with a u64_id identifier into the collection created in the example under dbo.vector_collection_create.
dbo.vector_point_upsert (
'my_collection',
'{
"u64_id" : 1,
"dim" : 3
}',
'[-0.1440905, -0.1729036, -0.1113158]',
'{"source": "individual", "idx": 1}'
);
This example updates this newly inserted point u64_id=1 to change the vector and payload.
dbo.vector_point_upsert (
'my_collection',
'{
"u64_id" : 1,
"dim" : 3
}',
'[-10.1440905, -10.1729036, -10.1113158]',
'{"source": "individual", "idx": "1u"}'
);
This example inserts a new point with a UUID identifier.
dbo.vector_point_upsert (
'my_collection',
'{
"uuid" : "123e4567-e89b-12d3-a456-426655440003",
"dim" : 3
}',
'[-0.1432765, -0.1863509, -0.2224269]',
'{"source": "individual", "uuidx": "123e"}'
);
This example inserts a new point with both u64_id and UUID identifiers. Since the UUID takes precedence, the u64_id is ignored.
dbo.vector_point_upsert (
'my_collection',
'{
"u64_id" : 2,
"uuid" : "8DBFABDD-F9F3-405A-BAE2-F7C499B9F497",
"dim" : 3
}',
'[-0.2542541, -0.2472479, -0.6516518]',
'{"source": "individual", "uuidx": "8DBF"}'
);
This example updates the point with UUID=8DBFABDD-F9F3-405A-BAE2-F7C499B9F497.
dbo.vector_point_upsert (
'my_collection',
'{
"uuid" : "8DBFABDD-F9F3-405A-BAE2-F7C499B9F497",
"dim" : 3
}',
'[-8.1440905, -8.1729036, -8.1113158]',
'{"source": "individual_2", "uuidx": "8DBFu"}'
);
dbo.vector_point_upsert_batch
This function inserts or updates multiple points in a collection.
Note:  A point is identified by its unique identifier, which can be either an 64-bit unsigned integer u64_id or a hexadecimal representation of a 128-bit UUID. Typically, a collection uses only one type of ID, rather than mixing IDs in the same collection. If valid IDs are present as both u64_id and UUID values, UUID takes precedence.
Syntax
dbo.vector_point_upsert_batch ( collection_name, u64_ids, uuids, vectors, payloads, count, dimension )
Arguments
Parameter
Type
Description
collection_name
STRING
Name of the target collection
u64_ids
JSON array
An array containing 64-bit unsigned integer IDs. Array size must equal count. If you are using UUIDs, use NULL for this argument.
uuids
JSON array
An array containing UUID strings. Array size must equal count. If you are using u64_id identifiers, use NULL for this argument.
vectors
JSON array
An array of vectors, where each item is an array of floats. The outer array size must equal count, and each inner array size must equal dimension.
payloads
JSON object
An object containing payload/metadata information. The array size must equal count.
count
INTEGER
The total number of points to insert in this batch.
dimension
INTEGER
The dimension of the collection.
Examples
This example inserts 3 points into a collection of dimension 3, using UUID identifiers. Note that the u64_id argument is set to NULL.
dbo.vector_point_upsert_batch (
'my_collection',
null,
'[ "3E02BC1F-9106-42A4-B73B-8EDBA2A39247",
"F11CBF12-9530-404D-9162-449923C51877",
"8240EC70-3761-4593-AED4-D4C02EBB15A0" ]',
'[
[-10.1440905, -10.1729036, -10.1113158],
[0.62034965, 0.3075915, -0.2218856 ],
[0.22034965, -0.3075915, 0.7218856 ]
]',
'[
{"source": "batch1", "uuidx": "3E02"},
{"source": "batch2", "uuidx": "F11C"},
{"source": "batch3", "uuidx": "8240E"}
]',
3,
3
);
This example updates 2 of the points inserted in the previous example.
dbo.vector_point_upsert_batch (
'my_collection',
null,
'[ "f11cbf12-9530-404d-9162-449923c51877",
"3e02bc1f-9106-42a4-b73b-8edba2a39247"
]',
'[
[-100.1440905, -100.1729036, -100.1113158],
[10.22034965, -0.3075915, 0.7218856 ]
]',
'[
{"source": "batch2_upd", "idx": "F11Cu"},
{"source": "batch1_upd", "idx": "3E02u"}
]',
2,
3
);
dbo.vector_point_delete
This function deletes a single point from a collection.
Note:  A point is identified by its unique identifier, which can be either an 64-bit unsigned integer u64_id or a hexadecimal representation of a 128-bit UUID. Typically, a collection uses only one type of ID, rather than mixing IDs in the same collection. If valid IDs are present as both u64_id and UUID values, UUID takes precedence.
Syntax
dbo.vector_point_delete ( collection_name, point_id )
Arguments
Parameter
Type
Description
collection_name
STRING
Name of the target collection.
point_id
JSON object
An object containing a unique identifier.
Examples
This example deletes a point inserted in the examples under dbo.vector_point_upsert.
dbo.vector_point_delete (
'my_collection',
'{ "u64_id" : 1 }' );
This example deletes a point inserted in the examples under dbo.vector_point_upsert_batch.
dbo.vector_point_delete (
'my_collection',
'{ "uuid" : "123e4567-e89b-12d3-a456-426655440003" }' );
dbo.vector_point_delete_batch
This function deletes multiple points from a collection.
Note:  A point is identified by its unique identifier, which can be either an 64-bit unsigned integer u64_id or a hexadecimal representation of a 128-bit UUID. Typically, a collection uses only one type of ID, rather than mixing IDs in the same collection. If valid IDs are present as both u64_id and UUID values, UUID takes precedence.
Syntax
dbo.vector_point_delete_batch ( collection_name, u64_ids, uuids, count )
Arguments
Parameter
Type
Description
collection_name
STRING
Name of the target collection
u64_ids
JSON array
An array containing 64-bit unsigned integer IDs. Array size must equal count. If you are using UUIDs, use NULL for this argument.
uuids
JSON array
An array containing UUID strings. Array size must equal count. If you are using u64_id identifiers, use NULL for this argument.
count
INTEGER
The total number of points to delete in this batch.
Example
This example deletes 2 points inserted in the examples under dbo.vector_point_upsert_batch.
dbo.vector_point_delete_batch (
'my_collection',
null,
'[ "8240ec70-3761-4593-aed4-d4c02ebb15a0", "8dbfabdd-f9f3-405a-bae2-f7c499b9f497"]',
2 );
;
dbo.vector_collection_search
This function is used in a SELECT statement to define a FROM clause in a query.
Syntax
dbo.vector_collection_search ( collection_name, null, query_vector, topN, dimension )
Arguments
Parameter
Type
Description
collection_name
STRING
Name of the collection to be searched.
null
NULL
Reserved.
query_vector
JSON array
An array of floats. Array size must equal dimension.
topN
INTEGER
The total number of points to return.
dimension
INTEGER
The dimension of the collection.
Results
The dbo.vector_collection_search function returns query results from a collection.
Examples
This example returns the results from a query, ordered by similarity score.
SELECT a.u64_id, a.uuid, a."SIMILARITY_SCORE"
FROM dbo.vector_collection_search (
'my_collection',
null,
'[1.3656894904327486, -1.303263971717364, -0.12213934352186545]',
10,
3 ) a
WHERE a."SIMILARITY_SCORE" > 0.1
ORDER BY a."SIMILARITY_SCORE" desc;
Result Set
U64_ID UUID SIMILARITY_SCORE
==================== ==================================== ================
(Null) 3e02bc1f-9106-42a4-b73b-8edba2a39247 0.73594624
5 (Null) 0.73594624
3 (Null) 0.7028858
4 (Null) 0.38268408
This example creates a user-defined function to use similarity scoring in a query.
create function f1 ( :a varchar(30), :score float ) returns float as
begin
return (select a."SIMILARITY_SCORE"
from dbo.vector_collection_search (
:a,
null,
'[1.3656894904327486, -1.303263971717364, -0.12213934352186545]',
100, 3 ) a
where a."SIMILARITY_SCORE" > :score
order by a."SIMILARITY_SCORE" desc limit 2, 1 );
end;
 
select f1 ( 'my_collection', 0.1 );
Result Set
EXPR_1
========================
0.7028858065605164
 
Last modified date: 08/05/2026