Conditions
Using conditions in queries means leaving only those records that satisfy conditions. If a corresponding field contains an attribute, it will be used to speed-up the query execution.
Here is an example with a single condition filter:
o)t:(+:)`a`b`c`d!(1 2 3;3 4 5;6 7 8;("111";"222";"333"));
o)c:,(<=;`b;3);
o)0N#.(?[t;c;0b;`a`b`c!`a`b`c])
a b c
-----
1 3 6
o)
Conditions may contain field expressions:
o)t: (+:)`a`b`c!(1 2 3;3 4 5;6 7 8);
o)c: ,(<=;(+;`b;`c);9);
o)0N#.(?[t;c;0b;`a`b`c!`a`b`c])
a b c
-----
1 3 6
o)
And/or/not clauses, simplified syntax
Of course, single field conditions are not enough, several conditions are trivially created using & and | dyads in condition list:
o)sym:`symbol$!10;
o)t:(+:)`a`b`c`d!(1 2 3;`g#`3`4`5;6 7 8;`g#`sym$`3`4`5);
o)c:,(&;(>=;`d;`sym$`3);(<=;`b;,`3));
o)0N#.(?[t;c;0b;`a`b`c!`a`b`c])
a b c
-----
1 3 6
o)
"not" clause is a bit awkward, as the ordinary ~ monad does not work here. Using ~: as monadic "not" is not possible because it results in "commute" instead. You need to use a special verb not:
o)t:(+:)`a`b`c!(!10;0+!10;`symbol$!10);
o)0N#.(?[t;,(not;(=;`a;6));0b;`a`b`c!`a`b`c])
a b c
-----
0 0 0
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
7 7 7
8 8 8
9 9 9
o)
Quite common pattern is joining all field conditions with "and" logic, thus a simplified syntax is also supported:
o)t:(+:)`a`b`c!(1 2 3;3 4 5;6 7 8);
o)c:((<=;`b;4);(>=;`c;7));
o)0N#.(?[t;c;0b;`a`b`c!`a`b`c])
a b c
-----
2 4 7
o)
Where "subselect"
Another commonly used pattern is a "sub-select". It applies another (sub-select) query to condition as an argument. It can be used as seen below:
o)t1:(+:)`a`b`c!(1 2 3;3 4 5;6 7 8);
o)t2:(+:)`b`c!(,2;,4);
o)c:,(=;`a;?[t2;();0b;(,`b)!(,`b)]);
o)0N#.(?[t1; c; 0b; `a`c!`a`c])
a c
---
2 7
o)
Pay attention that just a single scalar value is expected as a result of "t2" query, otherwise an error occurs:
o)t1: (+:)`a`b`c!(1 2 3;3 4 5;6 7 8);
o)t2:(+:)`b`c!(!2;!2);
o)c: ,(=;`a;?[t2;();0b;(,`b)!(,`b)]);
o)0N#.(?[t1; c; 0b; `a`c!`a`c])
** runtime error: `select`:
scalar expected
o)
Where "in"
Another predicate accepting sub-selects is in. It checks if the left arg is within the right argument set of values (either constant vector or a query).
o)t1:(+:)`a`b`c!(1 2 3;3 4 5;6 7 8);
o)t2:(+:)`b`c!(1 2;3 4);
o)c:,(in;`a;?[t2;();0b;(,`b)!(,`b)]);
o)0N#.(?[t1; c; 0b; `a`c!`a`c])
a c
---
1 6
2 7
o)
Where "like"
"Like" predicate matches against a regular expression. Both strings and symbols are supported.
o)t:((+:)`a`d`e`f!(1 2 3;3 4 5;6 7 8;("111";"222";"333")));
o)c:,(like;`f;"111|222");
o)0N#.(?[t; c; 0b; `a`f!`a`f])
a f
-------
1 "111"
2 "222"
o)
Where "match"
"Match" checks for exact equality.
o)t:((+:)`a`d`e`f!(1 2 3;3 4 5;6 7 8;("111";"222";"333")));
o)c: ,(~;`f;"111");
o)0N#.(?[t; c; 0b; `a`f!`a`f])
a f
-------
1 "111"
o)
Where with generic lambda
Any lambda monad/dyad can be used in place of predicate. However, it must satisfy the following requirements.
For monads:
- support vectors as arguments;
- rеturn resulting boolean vector of the argument's length.
o)t:(+:)`a`b`c!(1 2 3;0 0N 5;6 7 8);
o)0N#.(?[t;,(null;`b);0b;(`a`b)!(`a`b)])
a b
----
2 0N
o)
For dyads:
- support vectors in the left argument;
- support either vectors or scalars in the right argument (depending on the query predicate third parameter);
- rеturn resulting boolean vector of the left argument's length.
o) t:(+:)`a`b`c!(1 2 3;0 1 5;6 7 8);
o) 0N#.(?[t;,({x>y};`a;`b);0b;(`a`b)!(`a`b)])
a b
---
1 0
2 1
o)
Select from select
Selecting from inner select is also supported.
o)t1:((+:)`a`b`c`d!(1 2 3;3 4 5;6 7 8;8 9 10));
o)t2: ?[(`t1;t1);();0b;`a`b`c!`a`b`c];
o)c: ,(>=;`a;2);
o)0N#.(?[t2; c; 0b; `a`c!`a`c])
a c
---
2 7
3 8
o)