0

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

twboc
  • 1,452
  • 13
  • 17

1 Answers1

1

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.

Dheeraj
  • 106
  • 3