Get the overall average of all scores of a nested JSON.

(1) Share

Report

Posted on by OC-13081034-0

Hi,

i want to calculate the average over all scores.

{
    "single_results": [
        {
            "name": "Rule1",
                    "results": [
                        {
                            "name": "Sample1",
                            "positive_comment": "xxx",
                            "negative_comment": "xxx",
                            "score": 6
                        },
                        {
                            "name": "Sample2",
                            "positive_comment": "xxx",
                            "negative_comment": "xxx",
                            "score": 5
                        },
                        {
                            "name": "Sample3",
                            "positive_comment": "xxx",
                            "negative_comment": "xxx",
                            "score": 8
                        },
                        {
                            "name": "Sample4",
                            "positive_comment": "xxx",
                            "negative_comment": "xxx",
                            "score": 9
                        }
            ]
        },
        {
            "name": "Rule2",
                    "results": [
                        {
                            "name": "Sample5",
                            "positive_comment": "xxx",
                            "negative_comment": "xxx",
                            "score": 7
                        },
                        {
                            "name": "Sample6",
                            "positive_comment": "xxx",
                            "negative_comment": "xxx",
                            "score": 6
                        },
                        {
                            "name": "Sample7",
                            "positive_comment": "xxx",
                            "negative_comment": "xxx",
                            "score": 10
                        }
                    ]
        }
    ]
}

My code actually calculate the average of each rule and then the average of the average of this rules. Is it possible to calculate the average over all scores?

"Avg: "&   Average(
                ForAll(
                    ParseJSON(<JSONstring>).single_results As rec;
                    {name: Table(rec.result).Value; avgValue:Average(Table(rec.results);ThisRecord.Value.score)}
                );avgValue
             )

Categories:

General Questions

All responses (3)

Answers (1)

Verified answer

OC-13081034-0 13 on at

Like (1)

Report
Get the overall average of all scores of a nested JSON.

I have now following solution. I customize the JSON to following structure:

[ { "name": "Ruleset A", "results": [ { "name": "abc", "positive_comment": "This is good", "negative_comment": "This is bad", "score": 5 }, { "name": "cde", "positive_comment": "This is good", "negative_comment": "This is bad", "score": 7 } ] }, { "name": "Ruleset B", "results": [ { "name": "efg", "positive_comment": "This is good", "negative_comment": "This is bad", "score": 3 }, { "name": "ghi", "positive_comment": "This is good", "negative_comment": "This is bad", "score": 7 } ] } ]

and with following code, i extract every single score and get the overall average:

Average( Ungroup( ForAll( Table(ParseJSON(ThisItem.Result)), { Subtable: ForAll( RenameColumns(Table(Value.results),Value,Subvalue), {Rulescore: Value(Subvalue.score)} ) } ), Subtable ), Rulescore )
scalca 243 Super User 2025 Season 1 on at

Like (1)

Report

Get the overall average of all scores of a nested JSON.

I would suggest to use collections in order to bring this down

for example parse the single_results objects storing the score value into tables this will end up in your situation with two records
then I would loop through that collection and store the scores into a new collection and from there you could use the average function to calculate what you need

I stored the json string into a SPO list field this is where I'm getting it from and then use a variable to store the calculated average

ClearCollect(

AllScores,

ForAll(

ParseJSON(First('test Edited').jsondata).single_results,

ForAll(

ThisRecord.results,

{score: Value(ThisRecord.score)}

)

));

Clear(scoresNew);

ForAll(AllScores As rec, ForAll(rec.Value, Collect(scoresNew,{res: Value(ThisRecord.score)})));

Set(varScoresAvg, Average(scoresNew,res))
Suggested answer

DBO_DV 4,534 Super User 2025 Season 1 on at

Like (2)

Report
Get the overall average of all scores of a nested JSON.

Hey, OC-13081034-0

the easiest way to do this, is to add the table to a Gallery. This will be displayed like this :

There you just add another text label to display the average for every rule:

Average(ThisItem.results,score)

To get an Average for everything it is a bit more complicated since we've got a nested column and two columns have the same name. but this schould help you out:
Average( Ungroup( RenameColumns( Colsingle_results, name, rule ), results ), score )

Let me know if this solved your problem. If it did, please consider accepting this as solution so others can find it as well.

If it helped in any other way or form consider liking it so we can keep supporting each other.