How to store array in one column in Sqlite3?

Question

Is there any way to store an array of integers in one column of table? I want o/p like this:

ident | value                                                            | count 
----------------+------------------------------------------------------------------------------------------------------------------------+-------
563 | [0:10]={"(0,0)","(1,100)","(2,200)","(3,300)","(4,400)","(5,500)"} |    6

This I have already acheieved through postgres but i want same o/p from sqlite also. Here column value store an array. I tried it through BLOB but it is not working. Somebody told me about serialized way but i am not sure how to do that.

Answer 1

SQLite3 does not support arrays directly. See here the type it supports. Basically, it only does Ints, Floats and Text.

To accomplish what you need, you have to use a custom encoding, or use an FK, i.e. create another table, where each item in the array is stored as a row.

Answer 2

SQLite has no support for arrays, so you can't store them as such. Instead of storing array elements individually, you can store them as a single string and use a string function or regular expression to parse them back into their types.

A C# example:

int[] myArray = new int[] {8,4,345,378,34456,7};

string Arraystring = myArray[0].ToString();

for(int i = 1; i < myArray.Length; i++) { 
    Arraystring += "," + myArray[i].ToString();
}

This will turn the array into a single string, now insert it as string. To get the array back:

string value; //assign this via Reader
string[] tokens = values.Split(',');

int[] myItems = Array.ConvertAll<string, int>(tokens, int.Parse);

This will only work with single dimensional arrays, multi-dimensional can get tricky when it comes to parsing the strings.

Answer 3

Sqlite now has native support for json_object() data types since version 3.38:

You can store arrays in a JSON field, query them, and index them. More info is here: https://www.sqlite.org/json1.html.

For example, ask which guests like oranges:

create table guests (name, likes);
insert into guests values ('bob', json('["apples", "oranges"]'));
insert into guests values ('alice', json('["oranges", "pears"]'));
insert into guests values ('carol', json('["apples", "pears"]'));
select name from guests, json_each(likes) where json_each.value='oranges';

You can try it out here: https://sqlime.org/#deta:m97q76wmvzvd

Answer 4

You could use JSON.stringify and when you read in the data again JSON.parse. So you store an array as string and can easily parse it again back to an array. This way you can also put arrays of arrays into the sqlite db

Answer 5

This is one way of serializing and deserializing data:

#include <string>
#include <vector>
#include <sstream>
#include <iostream>

std::vector<std::string> deserialize_array(std::string const &csv)
{
  std::istringstream parse(csv);
  std::vector<std::string> ret;
  for(std::string token; std::getline(parse, token, ','); ret.push_back(token));
  return ret;
}

std::string serialize_array(std::string* array_ptr, std::size_t N)
{
  std::ostringstream cat;
  for(std::size_t index= 0; index< N; ++ index)
    cat<< array_ptr[index]<< ',';
  std::string ret= cat.str();
  return ret.substr(0, ret.size()-1);
}

int main()
{
  std::string data= "1,2,3";
  std::cout<< "Data: "<< data<< std::endl;
  std::vector<std::string> deserialized= deserialize_array(data);
  std::string serialized= serialize_array(deserialized.data(), deserialized.size());
  std::cout<< "Serialized + Deserialized: "<< serialized<< std::endl;
}

Instead of spending time parsing parentheses and extra commas, you can serialize as csv and read two by two when processing the deserialized data.

Answer 6

This is what I envision, though it may be incorrect:

<table>
  <citation>
    <citation ID>
    <citation content>
    <citation publication date>

CREATE TABLE citation
(
    citation_ID INTEGER PRIMARY KEY AUTOINCREMENT,
    citation VARCHAR(255)
    published datetime
    )


<table>
  <source doc>
    <source doc ID>
    <source doc content>

CREATE TABLE source
(
    source_ID INTEGER PRIMARY KEY AUTOINCREMENT,
    source VARCHAR(5000)
    )

<citation_to_source table> //table in question
  <relationship>
    <relationship ID>
    <citation ID>
    <source doc ID>

CREATE TABLE citation_to_source //table in question
(
    relationship_id INTEGER,
    citation_ID INTEGER,
            source_ID INTEGER,
            FOREIGN KEY(citation_ID) REFERENCES citation(citation_ID)
            FOREIGN KEY(source_ID) REFERENCES source(source_ID)
    )

Output format:

<content>
  <relationship ID>
  <unique source document content>
  <enumerate citation IDs>

Answer 7

I was dealing with the same issue. I came from JS but I think it'd be a cross-language answer. Since nobody gives a code example of the Gianni idea and Wolfpack'08 was asking for an example I'll leave my solution.

SELECT A.artistid, B.name, group_concat(A.title, '   --   ') titlesList 
FROM albums A
INNER JOIN artists B
ON A.artistid = B.artistid
GROUP BY A.artistid
LIMIT 10
;

• Notes:
  • I use SQLite Studio to get a better visual display and chinook.db database for the example.
  • A and B are tables alias in order to avoid ambiguous names.
  • I use ' -- ' to output a spaced list.
• Database resource:
  • https://www.sqlitetutorial.net/sqlite-sample-database/
• Docs: Built-in Aggregate Functions: group_concat(X,Y)
  • https://www.sqlite.org/lang_aggfunc.html#group_concat