Example 5:
In our final example we will demonstrate vector search with metadata filtering.
-- Create a new traditional table database examples;
create table ifx_sample_tab5 (
ident bigint,
embedding_data lvector_embedding, text_data lvarchar,
metadata lvarchar, primary key (ident)
);
-- Generate some real embeddings and insert them into our table insert into ifx_sample_tab5 values (
1,
lvector_embed('This text is about squirrels'), 'This text is about squirrels',
'Category: Animals'
);
insert into ifx_sample_tab5 values (
2,
lvector_embed('This text is about car racing'), 'This text is about car racing',
'Category: Modern Sports'
);
insert into ifx_sample_tab5 values (
3,
lvector_embed('This text is about architecture'), 'This text is about architecture',
'Category: The Arts'
);
insert into ifx_sample_tab5 values (
4,
lvector_embed('This text is about wrestling'), 'This text is about wrestling',
'Category: Ancient Sports'
);
insert into ifx_sample_tab5 values (
5,
lvector_embed('This text is about sound'), 'This text is about sound',
'Category: Music'
);
-- Create a lance table and synchronize it with the traditional one EXECUTE PROCEDURE lvector_create_table('sample_tab5', 1536);
EXECUTE PROCEDURE lvector_bulk_sync('ifx_sample_tab5', 'sample_tab5', 'ident', 'embedding_data');
-- Now in our previous example we ran a query that returned two rows relevant to sports: SELECT text_data
FROM ifx_sample_tab5 WHERE ident IN
(SELECT * FROM TABLE(lvector_id_search('sample_tab5', lvector_embed('Tell me about sports'), 2)));
Expected results:
text_data This text is about car racing
text_data This text is about wrestling
But what if we only want sports-related text returned if it definitely pertains to a modern sport, based on a keyword search of the metadata. We could filter on the metadata while performing the vector search:
SELECT text_data
FROM ifx_sample_tab5 WHERE ident IN
(SELECT results.unnamed_col_1.id FROM TABLE
(
lvector_search_filtered (
'sample_tab5',
lvector_embed('Tell me about sports'), 2,
'metadata like "%Modern%"'
)::lvector_search_result
) results
);