11.5 Recursively get field values

 

Calculate total sale amount in the year 2014 based on JSON-format sale data. Below is part of the JSON file:

[
    {"YEAR":2013,"MONTH":7,"SALES": [
        {"ID":10248,"CUSTOMERID":"VINET", … ,"SALES":2440},
        {"ID":10249,"CUSTOMERID":"TOMSP", … ,"SALES":1863.4},
        {"ID":10250,"CUSTOMERID":"HANAR", … ,"SALES":1813.0},
        …
    ]},
    {"YEAR":2013,"MONTH":8,"SALES": [
        {"ID":10270,"CUSTOMERID":"WARTH", … ,"SALES":1376.0},
        {"ID":10271,"CUSTOMERID":"SPLIR", … ,"SALES":48.0},
        {"ID":10272,"CUSTOMERID":"RATTC", … ,"SALES":1456.0},
        …
    ]},
    …
]

SPL uses A.field@r() function to get field values recursively.

SPL script:

A
1 =json(file(“sales.json”).read())
2 =A1.select(YEAR==2014)
3 =A2.field@r(“SALES”)
4 =A3.sum(~.sum())

A1 Import the JSON file.
A2 Get sale records of the year 2014.
A3 Use A.field@r() function to get SALES field values recursively.
A4 Calculate sale amount in each month of the year 2014 in loop and calculate the total.