Vectors/Lists in SQLite

Question

I am new to SQL, are there list or vector variable types? Can I have a column whose type is a list of strings?

Maybe something like:

CREATE TABLE myTbl(domain varchar PRIMARY KEY, linkList list<varchar>);

Can I append a string like so(by using the '+=' operator):

UPDATE myTbl SET linkList += "www.test.com," WHERE domain="blah";
// Table is like so
CREATE TABLE myTbl(domain varchar PRIMARY KEY, linkList varchar);
// Column linkList is a string of links separated by comma's

Answer 1

You can store lists in a SQLite field with the json1 extension: https://www.sqlite.org/json1.html

So your schema would look like:

CREATE TABLE myTbl(
    domain varchar PRIMARY KEY,
    linkList JSON DEFAULT('[]'));

and to append an item:

UPDATE myTbl
SET linkList = json_insert(linkList, '$[#]', "www.test.com")
WHERE domain="blah";

The SQLite JSON support is quite flexible, particularly with newer versions of SQLite. You can set up indices on JSON expressions for fast matching depending on the contents of your JSON. You can use it with generated columns for dynamic data extraction. You can SELECT from a JSON list with the json_each() table valued function.

Try copy-pasting this into on the sqlite3 command line or in https://sqliteonline.com/ :

CREATE TABLE myTbl(
    domain varchar PRIMARY KEY,
    linkList JSON DEFAULT('[]'));
INSERT INTO myTbl(domain) VALUES ("blah");

UPDATE myTbl
SET linkList = json_insert(linkList, '$[#]', "www.test.com")
WHERE domain="blah";

UPDATE myTbl
SET linkList = json_insert(linkList, '$[#]', "www.example.com")
WHERE domain="blah";

SELECT * FROM myTbl;

Note: This example requires SQLite 3.31 for the $[#] notation.