Nested JSON

Community Support
1

Hi,

How can I parse data in the following format into a table? 

{“result”:[[“ABC”,“MSFT”,“Equity”,{“display”:“1.23”,“raw”:5.55214},{“display”:“52.05”,“raw”:52.05}],[“DEF”,“GOOG”,“Equity”,{“display”:“2.34”,“raw”:4.38633},{“display”:“96.34”,“raw”:96.34}]]}

I’ve dropped it into a text file and have been trying the following code, by without success:

\l json.k

.j.k raze read0`:test1.txt;

flip a[`result]

I’d like to display this in a table and only need five columns: code, name, type and the two ‘raw’ values in the nested part of the JSON string.

2

Hi,

the problem here is the nested dictionary - to extract this data you can use indexing, then compose a table with a dictionary of column names and flip:

q)a:flip .j.k[read0[:test.txt] 0]result /prepare data

q)a[3 4]:a[3 4;`raw]                      /index intoneeded dictionary elements

q)t:flip codenametyperaw1`raw2!a      /convert to table

q)t

code  name   type     raw1    raw2

“ABC” “MSFT” “Equity” 5.55214 52.05

“DEF” “GOOG” “Equity” 4.38633 96.34

This indexing could more neatly be combined into a single step after reading, avoiding reassignment, by use of apply:

q)flip codenametyperaw1raw2!@[a;3 4;@;raw]

code  name   type     raw1    raw2

“ABC” “MSFT” “Equity” 5.55214 52.05

“DEF” “GOOG” “Equity” 4.38633 96.34

Hope this is useful,

Ryan

From: personal-kdbplus@googlegroups.com <personal-kdbplus@googlegroups.com> on behalf of Student <tstannes@gmail.com>
Sent: 02 June 2018 20:26:33
To: Kdb+ Personal Developers
Subject: [personal kdb+] Nested JSON
 

Hi,

How can I parse data in the following format into a table? 

{“result”:[[“ABC”,“MSFT”,“Equity”,{“display”:“1.23”,“raw”:5.55214},{“display”:“52.05”,“raw”:52.05}],[“DEF”,“GOOG”,“Equity”,{“display”:“2.34”,“raw”:4.38633},{“display”:“96.34”,“raw”:96.34}]]}

I’ve dropped it into a text file and have been trying the following code, by without success:

\l json.k

.j.k raze read0`:test1.txt;

flip a[`result]

I’d like to display this in a table and only need five columns: code, name, type and the two ‘raw’ values in the nested part of the JSON string.

–
Submitted via Google Groups

3

Here’s one very simple (qsql) way to parse that bit of text into a table…

q)colms:codenametiperaw1`raw2

q)update raw1:raw1[;raw],raw2:raw2[;raw] from colms!/:.j.k[raze read0:file]result

code  name   tipe     raw1    raw2

“ABC” “MSFT” “Equity” 5.55214 52.05

“DEF” “GOOG” “Equity” 4.38633 96.34

But tbh, it might be better to try and get the json output changed (if possible) so as you can read in directly with .j.k.

Also, the .j namespace doesn’t need loaded in later version of kdb+, so if possible too, try to update your version (its faster etc)

Regards