learnkdb
Back to Converting Dictionaries to Tables

Dictionaries · Converting Dictionaries to Tables · Practice Question

Constructing a Table from Non-Conforming Dictionaries

Sign in to track your progress

Question

A trading system receives JSON messages from several independent systems. Each system publishes slightly different information, so the resulting dictionaries do not all have the same schema.

Create a file called reports.json containing the following JSON messages (one message per line):

{"orderId":1001,"sym":"AAPL","qty":100,"px":150.5,"status":"Filled"}
{"venue":"XNYS","sym":"MSFT","px":151.0,"qty":200,"status":"PartialFill"}
{"account":"ACC1","sym":"GOOG","risk":12.5,"px":149.8,"qty":300,"status":"Filled"}

Read the file, parse the JSON messages into dictionaries, and construct a table containing only the fields that are present in every dictionary.

Expected result:

sym    qty px    status
------------------------------
"AAPL" 100 150.5 "Filled"
"MSFT" 200 151   "PartialFill"
"GOOG" 300 149.8 "Filled"

Solution

reports:.j.k each read0 `:reports.json
((inter/) key each reports) #/: reports

Explanation

First read the file and parse each JSON message:

reports:.j.k each read0 `:reports.json

Result:

`orderId`sym`qty`px`status!(1001f;"AAPL";100f;150.5;"Filled")
`venue`sym`px`qty`status!("XNYS";"MSFT";151f;200f;"PartialFill")
`account`sym`risk`px`qty`status!("ACC1";"GOOG";12.5;149.8;300f;"Filled")

These dictionaries do not conform because they contain different sets of keys:

key each reports

Result:

`orderId`sym`qty`px`status
`venue`sym`px`qty`status
`account`sym`risk`px`qty`status

To find the keys common to all dictionaries:

(inter/) key each reports

Result:

`sym`qty`px`status

These common keys are then extracted from each dictionary:

((inter/) key each reports) #/: reports

The resulting dictionaries now have identical keys in identical order, meaning they are conforming dictionaries.

When q encounters a list of conforming dictionaries, it automatically promotes the result to a table:

sym    qty px    status
------------------------------
"AAPL" 100 150.5 "Filled"
"MSFT" 200 151   "PartialFill"
"GOOG" 300 149.8 "Filled"

Note that .j.k parses every JSON number as a float, so qty is a float column, and JSON strings become q strings (character vectors), which is why sym and status are shown in quotes.

This is a common technique when normalising data received from multiple systems that provide overlapping but non-identical schemas.