| spath {}.orderTypesTotal{} output=orderTypesTotal | mvexpand orderTypesTotal | spath input=orderTypesTotal | stats sum(totalFailedTransactions) as totalFailedTransaction sum(totalSuccessfulTransactions) as totalSuccessfulTransactions sum(totalTransactions) as totalTransactions by orderType

This is a task for spath and JSON functions, not for rex.  Important: Do not treat structured data as strings.  But most importantly, if there is a worse data design in JSON than this, I haven't seen it before. (Trust me, I've seen many bad JSON designs.)  Your developers really go to lengths to show the world how lazy they can be.  After lengthy reverse engineering, I cannot decide whether this is the poorest transliteration of an existing data table into JSON, or the worst imagined construction of a data table in JSON.  Using two separate arrays to describe row and column saves a little bit of space, but unlike in a SQL database, this makes search less efficient. (Unless the application reads the whole thing and reconstruct data into an in-memory SQL database.  It also wastes text to describe data type in each column when you JSON format has enough datatypes to include strings and numbers. Enough ranting.  If you have any influence over your developers, make them change the event format to something like this instead:   [{"id":"BM0077","event":35602782,"delays":3043.01,"drops":0},{"id":"BM0267","event":86497692,"delays":1804.55,"drops":44},{"id":"BM059","event":1630092,"delays":5684.5,"drops":0},{"id":"BM1604","event":2920978,"delays":4959.1,"drops":2},{"id":"BM1612","event":2141607,"delays":5623.3,"drops":6},{"id":"BM1834","event":74963092,"delays":2409,"drops":8},{"id":"BM2870","event":41825122,"delays":2545.34,"drops":7}]   With properly designed JSON data, extraction can be simple as    ``` extract from well-designed JSON ``` | spath path={} | mvexpand {} | spath input={} | fields - _* {}   Output will your sample data (see emulation below) will be delays drops event id 3043.01 0 35602782 BM0077 1804.55 44 86497692 BM0267 5684.5 0 1630092 BM059 4959.1 2 2920978 BM1604 5623.3 6 2141607 BM1612 2409 8 74963092 BM1834 2545.34 7 41825122 BM2870  This is how to reach good JSON structure from the sample's bad structure:   ``` transform from bad JSON to well-designed JSON ``` | spath path={} | mvexpand {} | fromjson {} | mvexpand rows | eval idx = mvrange(0, mvcount(columns)) | eval data = json_object() | foreach idx mode=multivalue [eval row = mvindex(json_array_to_mv(rows), <<ITEM>>), data = json_set(data, json_extract(mvindex(columns, <<ITEM>>), "text"), row)] | stats values(data) as _raw | eval _raw = mv_to_json_array(_raw, true())   But before your developer can change their mind, you can do one of the following. 1. String together the bad-JSON-to-good-JSON transformation and normal extraction   ``` transform, then extract ``` ``` transform from bad JSON to well-designed JSON ``` | spath path={} | mvexpand {} | fromjson {} | mvexpand rows | eval idx = mvrange(0, mvcount(columns)) | eval data = json_object() | foreach idx mode=multivalue [eval row = mvindex(json_array_to_mv(rows), <<ITEM>>), data = json_set(data, json_extract(mvindex(columns, <<ITEM>>), "text"), row)] | stats values(data) as _raw | eval _raw = mv_to_json_array(_raw, true()) ``` extract from well-designed JSON ``` | spath path={} | mvexpand {} | spath input={} | fields - _* {}   2. Transform the poorly designed JSON into table format with a little help from kv aka extract, like this:   ``` directly handle bad design ``` | spath path={} | mvexpand {} | fromjson {} | fields - _* {} type | mvexpand rows | eval idx = mvrange(0, mvcount(columns)) ``` the above is the same as transformation ``` | foreach idx mode=multivalue [eval _raw = mvappend(_raw, json_extract(mvindex(columns, <<ITEM>>), "text") . "=" . mvindex(json_array_to_mv(rows), <<ITEM>>))] | fields - rows columns idx | extract   This will give you the same output as above. Here is a data emulation you can play with the above methods and compare with real data   | makeresults | eval _raw = "[{\"columns\":[{\"text\":\"id\",\"type\":\"string\"},{\"text\":\"event\",\"type\":\"number\"},{\"text\":\"delays\",\"type\":\"number\"},{\"text\":\"drops\",\"type\":\"number\"}],\"rows\":[[\"BM0077\",35602782,3043.01,0],[\"BM1604\",2920978,4959.1,2],[\"BM1612\",2141607,5623.3,6],[\"BM2870\",41825122,2545.34,7],[\"BM1834\",74963092,2409.0,8],[\"BM0267\",86497692,1804.55,44],[\"BM059\",1630092,5684.5,0]],\"type\":\"table\"}]" ``` data emulation above ```   A final note: spath has some difficulty handling array of arrays, so I used fromjson (available since 9.0) in one filter.