I have a kdb table with a format that could be summarized with
app type labels
--------------------------------------------------------
app1 frontend ("language:js";"owner:bob";"license:GPL")
app2 backend ("language:go";"owner:alice")
I cannot figure out how to "explode" the labels into their own column directly into that table and obtain something like
app type language owner license
-------------------------------------------
app1 frontend "js" "bob" "GPL"
app2 backend "go" "alice" ""
(note the optional kv)
How can I achieve this ? I feel like I am missing something very simple...
Firstly, don't use type as a column name. It is a reserved keyword in kdb+.
The idea is pull out the labels column, manipulate that individually and then rejoin it record by record to the original table.
This is a first pass, which gets what you want but is somewhat messy:
q)t:([]app:`app1`app2;Type:`frontend`backend;labels:(("language:js";"owner:bob";"license:GPL");("language:go";"owner:alice")))
q)delete labels from t,'(uj/)exec enlist each(!/)@/:flip each"S*"$/:/:":"vs/:/:labels from t
app Type language owner license
--------------------------------------
app1 frontend "js" "bob" "GPL"
app2 backend "go" "alice" ""
A shorter version:
q)delete labels from t,'(uj/)(enlist(!/)flip"S*"$/:":"vs/:)each t`labels
app Type language owner license
--------------------------------------
app1 frontend "js" "bob" "GPL"
app2 backend "go" "alice" ""
Abstracting the parsing logic into a separate function for cleanliness:
q)f:enlist(!/)flip"S*"$/:":"vs/:
q)delete labels from t,'exec(uj/)f each labels from t
It is also worth mentioning 0:, which can be used to split key value pairs inside character lists, and can cast keys to appropriate types (https://code.kx.com/q/ref/file-text/#key-value-pairs). The tradeoff is that 0: will cast to a list, so we need to reduce that to an atom before we can uj the records into a table, whereas with vs, we have to do the casting ourselves:
q)f:enlist(!/)flip(first;::)@'/:"S: "0:/:
q)delete labels from t,'exec(uj/)f each labels from t