How to query KDB table where one column is a list?

Question

I have a table in KDB where one of the columns is a list of symbols, for example:-

q)table:([id:1 2 3 4 5] books:((`book1`book2);(enlist `book6);(enlist`book3);(enlist`book4);(`book2;`book5)))
q)table
id| books
--| ------------
1 | `book1`book2
2 | ,`book6
3 | ,`book3
4 | ,`book4
5 | `book2`book5
q)

I want to find all the rows where `book2 is one of the books in the books column, how do I do this? So in the example above, I'd expect the 1st and 5th rows to be returned. I have tried various things using the "in" operator but either get an error or no results, for example :-

q)select from books where `book2 in books
clientTradeId| book
-------------| ----
q)select from books where enlist `books2 in books
'length

score 4 · Answer 1 · edited Dec 12 '17 at 16:40

4

select from table where `book2 in/:books

is valid, using the eachright adverb.

edited Dec 12 '17 at 16:40

Thomas Smyth - Treliant

4,993
6
25
36

answered Oct 17 '16 at 16:36

Paul Kerrigan

465
5
12

Thanks, this worked as well. Is there any reason why one might choose one adverb over the other? e.g. performance? Readability? – Alan Chan Oct 18 '16 at 12:39
2

In my opinion the each-right (/:) is better syntax in this case. Each-both (') is intended for item-wise operations with an equal number of items in the left list and the right list. In the other example there is only one item in the left list (`book2) but there are multiple items in the right list (the book column) - however kdb just happens to allow it in the single item left list case. Each-both wouldn't work for example if you were looking for a pair in each list on the right – terrylynch Oct 18 '16 at 15:16
1

For example compare "1 2 in/:((1 2;3 4);(5 6;7 8);(1 2;9 10))" to "1 2 in'((1 2;3 4);(5 6;7 8);(1 2;9 10))". It all depends on the use-case though – terrylynch Oct 18 '16 at 15:20
1

eachboth uses slightly less RAM (on the order of bytes) because it's more terse - as @terrylynch said above the eachright is better syntax because it allows you to use a list as the left argument - for example: `Valid:select from table where min each `book1`book2 in/: books` `Invalid:select from table where min each `book1`book2 in' books` – Paul Kerrigan Oct 19 '16 at 13:53

score 2 · Accepted Answer · edited Dec 03 '17 at 10:27

2

select from table where `book2 in'books

This utilises the adverb each-both, read more about it here: http://code.kx.com/q4m3/6_Functions/#672-each-both

edited Dec 03 '17 at 10:27

clemens

16,716
11
50
65

answered Oct 17 '16 at 16:06

jomahony

1,662
6
9

score 1 · Answer 3 · answered Jul 09 '18 at 19:59

Another useful variation where you want to match any/all of the input books to the books column :

1) This will return the rows which have both book2 and book5 (using all each):

q)select from table where  all each `book2`book5 in/:books
id| books
--| -----------
5 | book2 book5

2) This will return the rows which have either book2 or book4 (using any each)

q)select from table where  any each `book4`book5 in/:books
id| books
--| ------------
4 | ,`book4
5 | `book2`book5

How to query KDB table where one column is a list?

3 Answers3

Linked