CODEWITHSUNDEEP
kdb+
cjm2671
cjm2671

Reputation: 19506

How do I query a list in a field?

I have a table of the form:

t                             p      s    c
----------------------------------------------------------
2019.08.06D13:29:45.746200000 4.5    4    (," ";,"T";,"I")
2019.08.06D13:29:58.413500000 4.5148 14   (," ";,"T";,"I")
2019.08.06D13:30:00.072000000 4.65   1522 (," ";,"O")
2019.08.06D13:30:03.681300000 4.5048 1    (," ";,"I")
2019.08.06D13:30:04.045200000 4.5    5    (," ";,"I")

I'm trying to grab the row at 13:00, that contains "O" in the column c.

I think it should be something like:

select from t where "O" in c

but it doesn't seem to work (null result). I've tried raze on this column too, but it doesn't help. What am I doing wrong?

Edit with raze each c:

2019.08.06D13:29:45.746200000 4.5    4    " TI"
2019.08.06D13:29:58.413500000 4.5148 14   " TI"
2019.08.06D13:30:00.072000000 4.65   1522 " O"
2019.08.06D13:30:03.681300000 4.5048 1    " I"
2019.08.06D13:30:04.045200000 4.5    5    " I"

Upvotes: 1

Views: 188

Answers (2)

Matt Moore
Matt Moore

Reputation: 2800

Alternative approach for this simple example with index:

q)select from t where c[;1;0] = "O"
c
---------
," " ,"O"

Although likely this could be broken if you aren't looking for "O" to be the 2nd item in each list. Also note this only works with = because it gets down to a character instead of a string (which is a list of characters)

Upvotes: 0

Cathal O'Neill
Cathal O'Neill

Reputation: 3179

You need to enlist"0" as that is the form the c values take. In combination with each-right (/:) you can check if enlist"0" is in each c value:

q)t:([]c:(enlist each(" ";"T";"I");enlist each(" ";"T";"I");enlist each(" ";"O");enlist each(" ";"I");enlist each(" ";"I")))
q)select from t where enlist["O"]in/:c
c
---------
," " ,"O"

No need for the enlist if you raze the result:

q)select from(update raze each c from t)where"O"in/:c
c
----
" O"

Upvotes: 3

Related Questions