CODEWITHSUNDEEP
sqlarraysdatabasevoltdb
twboc
twboc

Reputation: 1607

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

Upvotes: 0

Views: 335

Answers (1)

Dheeraj
Dheeraj

Reputation: 106

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.

Upvotes: 1

Related Questions