JSON & casting types

Community Support
1

I’m struggling with JSON decoding in KDB.

I’m finding it very difficult to convert arbitrary JSON to tables. The main problem is casting types:

acceptingOrders active volume24hr slug                                      
----------------------------------------------------------------------------
""              1      ""         "will-the-match-be-a-draw-romania-ukraine"
1b              1      4466.708   "fed-rate-hike-in-2025"                   
1b              1      2703.898   "us-recession-in-2025"                    
1b              1      12775.76   "fed-emergency-rate-cut-in-2025"          
1b              1      759.7623   "tether-insolvent-in-2025"

Actual code:

a10: .j.k .Q.hg`$":https://gamma-api.polymarket.com/markets?closed=false&limit=500"
c10: asc distinct raze key each a10
a20: {c10#x} each a10 
c20: `acceptingOrders`active`volume24hr`slug
a30: c20#a20
a40: flip"bbf*"$flip a30

The issue I’m having is with nulls. For example, null char lists of length 0 "” are not null. I think in principle I’d be happy to cast by column, but neither Tok nor Cast seem to much like the mixed types either.

I’ve spent several hours trying to figure this out. I suspect I can probably write a complicated program that does this, surely there has to be a one-liner to fix it all?

Any help or thoughts would be much appreciated.

2

Handing JSON does take some extra work due to its limitations as a format.
I wrote a blog post on this before with some tips which you find find helpful

Set up same as yours up to a30:

a10: .j.k .Q.hg`$":https://gamma-api.polymarket.com/markets?closed=false&limit=500"
c10: asc distinct raze key each a10
a20: {c10#x} each a10 
c20: `acceptingOrders`active`volume24hr`slug
a30: c20#a20

Then use two reusable helpers, generalHelper and swapEmptyString which make it easier to break down the task so you can create custom rules per column which needs cleaning up:

generalHelper:{[t;d] ![t;();0b;key[d]!{(x;y)}'[value d;key d]]}
swapEmptyString:{@[x;where ""~/:x;:;y]}
castRules:`acceptingOrders`active`volume24hr!(swapEmptyString[;0b];swapEmptyString[;0b];swapEmptyString[;0Nf])
a40:generalHelper[;castRules] a30

The table now has uniformly typed columns:

meta a40
c              | t f a
---------------| -----
acceptingOrders| b
active         | b
volume24hr     | f
slug           | C

Blog link: