kdbq

Convert a column of dictionary datatype to table in kdb


I was trying to convert a column which has a dictionary datatype on a table to a separate table to fetch information from it easily. This works with a simple uj for me when the dictionaries have the same set of keys. E.g. is shown below, which gives me a table with column names x and y as each dictionary in the table had two of the same keys.

q)t1:([] a:1 2 3 ; b: `a`b`c; d:(`x`y!(1 2); `x`y!(11 22); `x`y!(111 222)))
q){(uj/) value x }each select d from t1
x   y
-------
1   2
11  22
111 222

When I try and do this with a table which has a dictionary with different keys, shown below as 2 of these has keys x and y while the last one also has z in it. This would return me a kind of list of dictionaries rather than a table structure, how can I handle this?

q)t1:([] a:1 2 3 ; b: `a`b`c; d:(`x`y!(1 2); `x`y!(11 22); `x`y`z!(111 222 333)))
q){(uj/) value x }each select d from t1
`x`y!1 2
`x`y!11 22
`x`y`z!111 222 333
q)

Solution

  • One method:

    q)(uj/)enlist each exec d from t1
    x   y   z
    -----------
    1   2
    11  22
    111 222 333
    

    enlist each is converting each dictionary to a table and (uj/) will return a union of their columns.

    FWIW, in this scenario exec d from t1 has the same output as {(uj/) value x }each select d from t1

    q)(exec d from t1)~{(uj/) value x }each select d from t1
    1b