🔍

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)

List enclosure in "c" definition is mandatory. Condition must be a proper list!

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;
  • return 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);
  • return 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)

<<< prev SELECT
next >>> ORDER