Search code examples
sqlarraysdatabasevoltdb

how to make a array query in voltDB?

Is it possible to have an array column in voltDB or to query a nested array in json type column?

For exapmle is it possible to do a query: Find rows where array contains a value.

In other words

  1. array [1,2,3,4]
  2. array [2,3,4,5]
  3. array [3,4,5,6]

And find where array contains 1 returns row 01 find where array contains 3 returns rows 01, 02, 03 find where array contains 5 returns 02, 03

Solution

  • While there are no Array column types in VoltDB, this can be addressed by using JSON. For example, say we have the following table definition:

    CREATE TABLE JSONDocContainer (
idCol BIGINT NOT NULL,
jsonDoc VARCHAR(2048) NOT NULL
);

PARTITION TABLE JSONDocContainer ON COLUMN idCol;

    The jsonDoc has a value as follows:

    {"Info": {"FirstName":"Dheeraj", "LastName":"Remella", "Grades":["A","B","B","C","A","F"]}}

    Then you can run a query as follows:

    select POSITION('["F",' IN FIELD(FIELD(JSONDOC, 'Info'), 'Grades')), 
POSITION(',"F",' IN FIELD(FIELD(JSONDOC, 'Info'), 'Grades')), 
POSITION(',"F"]' IN FIELD(FIELD(JSONDOC,'Info'), 'Grades')) 
from jsondoccontainer where idcol=2;

    Returns:

    C1  C2  C3 
--- --- ---
  0   0  21

    If C1 or C2 or C3 come back as non zero, then it exists. While this is not as elegant as something like "column contains value" kind of a syntax, this will work.