'Vectors/Lists in SQLite

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
sqlsqlite


Solution 1:[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.

Solution 2:[2]

There is no list datatype in SQLite.

But you can workaround that limitation:

  1. Convert your list to JSON (that includes escaping each element individually),
  2. Put the JSON string into your table as JSON1 (or SQLITE_TEXT if your version of SQLite is before SQLite 3.9).

Obviously, JSON has a big performance impact, and adding an element to your list will take time (read, deserialize, add, serialize, update).

Solution 3:[3]

As far as I know, it is not possible in SQLite (probably that is one of the reasons for 'Lite' in the name). Under the hood it only has storage types: 64-bit integer SQLITE_INTEGER, 64-bit float (double) SQLITE_FLOAT, blob (binary) SQLITE_BLOB and string SQLITE_TEXT. All SQL types are mapped to these. There is also SQLITE_NULL.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 sirgeorge

Related Questions