Search in array

SQL++
1

Hi @vsr1,

I have another query which is giving me hard times:

I have multiple documents of the format:

{
“gm”:[
“1-1-2”,
“1-10-1”,
“1-12-2”,
“1-2-2”,
“1-3-2”,
“1-4-3”,
“1-5-2”,
“1-6-3”,
“1-7-3”,
“1-8-2”,
“1-9-1”,
“2-1-16”,
“2-1-18”,
“2-1-20”,
“2-1-9”,
“2-2-11”,
“2-2-12”,
“2-2-13”,
“2-2-14”,
“2-2-15”,
“2-2-16”,
“2-2-17”,
“2-2-46”,
“2-2-47”,
“2-2-53”,
“2-2-58”,
“2-2-70”,
“2-2-84”,
“3-1-18”,
“3-1-21”,
“4-1-1”,
“4-1-10”,
“4-1-11”,
“4-1-18”,
“4-1-2”,
“4-1-3”,
“4-2-13”,
“4-2-15”,
“4-2-16”,
“4-2-3”,
“4-2-8”,
“4-3-8”,
“4-4-7”,
“4-4-8”,
“4-4-9”,
“4-5-9”,
“4-6-8”
],
“type”:“app”
}

and I would like to do a query in which I need to do some combinations of “gm” values

something like

gm contains ANY of( “4-3-8” ,“4-5-9” ) and contains ALL of (“2-2-46” , “2-2-14”)

I know how to searching for one value but not how to have ANY and ALL :

select * from `default` a  where a.type = 'app' 
  AND ANY t IN a.gm SATISFIES t = "4-3-8" END

I could not find any examples with something similar with what I need

2

@flaviu

SELECT * 
FROM `default` a  
WHERE a.type = 'app' 
  AND ANY t IN  a.gm SATISFIES t  IN ["4-3-8", "4-3-9"] END
  AND ANY AND EVERY v IN [ "2-2-46" , "2-2-14" ] SATISFIES  v IN a.gm END

ANY or SOME, EVERY, and ANY AND EVERY or SOME AND EVERY

Range predicates (ANY or SOME, EVERY, and ANY AND EVERY or SOME AND EVERY) allow you to test a boolean condition over the elements or attributes of a collection or object(s). They each evaluate to a boolean value.

ANY or SOME is TRUE if the collection is non-empty and at least one element matches.

EVERY is TRUE if the collection is empty, or if the collection is non-empty and every element matches.

ANY AND EVERY or SOME AND EVERY is TRUE if the collection is non-empty and every element matches.

range-cond:

var:

name-var:

3

Yeap,

it is working, thank you.

Is there any way in which I could create an index to accelerate the AND ANY AND EVERY v IN [ “2-2-46” , “2-2-14” ] SATISFIES v IN a.gm END ?

4

IN [ “2-2-46” , “2-2-14” ] is constant it is not possible.

The following one use array index and gives results.

SELECT * 
FROM `default` a  
WHERE a.type = 'app' 
  AND ANY t IN  a.gm SATISFIES t  IN ["4-3-8", "4-3-9"] END
  AND ANY t IN  a.gm SATISFIES t  = "2-2-46"  END
  AND ANY t IN  a.gm SATISFIES t   =  "2-2-14" END
1 Like
5

ok, I will test more and see how slow will be in some real life results…

Thank you so much for your help, I really appreciate answering me Sunday…