Issue processing nested json string in json object

Grafana Dashboards
,
1

Hello everyone,I’ve got an issue that I can’t seem to resolve so maybe you can help me with this.I have the infinity datasource configured to make querys to the sonarQube API.I need to get the success rate of the quality gate in sonar, to do so I have an endpoint wich gives me the detail history of those quality gates. Here is an response example of this endpoint:

{
    "paging": {
        "pageIndex": 1,
        "pageSize": 100,
        "total": 13
    },
    "measures": [
        {
            "metric": "quality_gate_details",
            "history": [
                {
                    "date": "2024-11-28T14:24:31+0100",
                    "value": "{\"level\":\"OK\",\"conditions\":[{\"metric\":\"new_blocker_violations\",\"op\":\"GT\",\"period\":1,\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"}],\"ignoredConditions\":false}"
                },
                {
                    "date": "2024-12-05T11:44:23+0100",
                    "value": "{\"level\":\"ERROR\",\"conditions\":[{\"metric\":\"new_blocker_violations\",\"op\":\"GT\",\"period\":1,\"error\":\"0\",\"actual\":\"3\",\"level\":\"ERROR\"},{\"metric\":\"test_errors\",\"op\":\"GT\",\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"}],\"ignoredConditions\":false}"
                },
                {
                    "date": "2024-12-19T10:15:05+0100",
                    "value": "{\"level\":\"ERROR\",\"conditions\":[{\"metric\":\"new_reliability_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_security_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_maintainability_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_coverage\",\"op\":\"LT\",\"period\":1,\"error\":\"80\",\"actual\":\"0.0\",\"level\":\"ERROR\"},{\"metric\":\"new_duplicated_lines_density\",\"op\":\"GT\",\"period\":1,\"error\":\"3\",\"actual\":\"0.0\",\"level\":\"OK\"},{\"metric\":\"new_blocker_violations\",\"op\":\"GT\",\"period\":1,\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"},{\"metric\":\"new_line_coverage\",\"op\":\"LT\",\"period\":1,\"error\":\"50\",\"actual\":\"0.0\",\"level\":\"ERROR\"},{\"metric\":\"test_errors\",\"op\":\"GT\",\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"},{\"metric\":\"test_failures\",\"op\":\"GT\",\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"}],\"ignoredConditions\":false}"
                },
                {
                    "date": "2024-12-19T11:38:59+0100",
                    "value": "{\"level\":\"OK\",\"conditions\":[{\"metric\":\"new_reliability_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_security_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_maintainability_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_blocker_violations\",\"op\":\"GT\",\"period\":1,\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"},{\"metric\":\"test_errors\",\"op\":\"GT\",\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"},{\"metric\":\"test_failures\",\"op\":\"GT\",\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"}],\"ignoredConditions\":false}"
                },
                {
                    "date": "2024-12-19T12:01:07+0100",
                    "value": "{\"level\":\"ERROR\",\"conditions\":[{\"metric\":\"new_reliability_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_security_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_maintainability_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_coverage\",\"op\":\"LT\",\"period\":1,\"error\":\"80\",\"actual\":\"0.0\",\"level\":\"ERROR\"},{\"metric\":\"new_duplicated_lines_density\",\"op\":\"GT\",\"period\":1,\"error\":\"3\",\"actual\":\"20.291251478820847\",\"level\":\"ERROR\"},{\"metric\":\"new_blocker_violations\",\"op\":\"GT\",\"period\":1,\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"},{\"metric\":\"new_line_coverage\",\"op\":\"LT\",\"period\":1,\"error\":\"50\",\"actual\":\"0.0\",\"level\":\"ERROR\"},{\"metric\":\"test_errors\",\"op\":\"GT\",\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"},{\"metric\":\"test_failures\",\"op\":\"GT\",\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"}],\"ignoredConditions\":false}"
                },
                {
                    "date": "2024-12-19T12:35:45+0100",
                    "value": "{\"level\":\"OK\",\"conditions\":[{\"metric\":\"new_reliability_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_security_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_maintainability_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_duplicated_lines_density\",\"op\":\"GT\",\"period\":1,\"error\":\"3\",\"actual\":\"0.0\",\"level\":\"OK\"},{\"metric\":\"new_blocker_violations\",\"op\":\"GT\",\"period\":1,\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"},{\"metric\":\"test_errors\",\"op\":\"GT\",\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"},{\"metric\":\"test_failures\",\"op\":\"GT\",\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"}],\"ignoredConditions\":false}"
                },
                {
                    "date": "2024-12-19T14:14:03+0100",
                    "value": "{\"level\":\"ERROR\",\"conditions\":[{\"metric\":\"new_reliability_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"3\",\"level\":\"ERROR\"},{\"metric\":\"new_security_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_maintainability_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_coverage\",\"op\":\"LT\",\"period\":1,\"error\":\"80\",\"actual\":\"0.0\",\"level\":\"ERROR\"},{\"metric\":\"new_duplicated_lines_density\",\"op\":\"GT\",\"period\":1,\"error\":\"3\",\"actual\":\"20.199617753238478\",\"level\":\"ERROR\"},{\"metric\":\"new_blocker_violations\",\"op\":\"GT\",\"period\":1,\"error\":\"0\",\"actual\":\"2\",\"level\":\"ERROR\"},{\"metric\":\"new_line_coverage\",\"op\":\"LT\",\"period\":1,\"error\":\"50\",\"actual\":\"0.0\",\"level\":\"ERROR\"},{\"metric\":\"test_errors\",\"op\":\"GT\",\"error\":\"0\",\"actual\":\"4\",\"level\":\"ERROR\"},{\"metric\":\"test_failures\",\"op\":\"GT\",\"error\":\"0\",\"actual\":\"2\",\"level\":\"ERROR\"}],\"ignoredConditions\":false}"
                },
                {
                    "date": "2025-01-06T17:51:47+0100",
                    "value": "{\"level\":\"ERROR\",\"conditions\":[{\"metric\":\"new_reliability_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_security_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_maintainability_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_coverage\",\"op\":\"LT\",\"period\":1,\"error\":\"80\",\"actual\":\"0.0\",\"level\":\"ERROR\"},{\"metric\":\"new_duplicated_lines_density\",\"op\":\"GT\",\"period\":1,\"error\":\"3\",\"actual\":\"0.010933675111918313\",\"level\":\"OK\"},{\"metric\":\"new_blocker_violations\",\"op\":\"GT\",\"period\":1,\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"},{\"metric\":\"new_line_coverage\",\"op\":\"LT\",\"period\":1,\"error\":\"50\",\"actual\":\"0.0\",\"level\":\"ERROR\"},{\"metric\":\"test_errors\",\"op\":\"GT\",\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"}],\"ignoredConditions\":false}"
                },
                {
                    "date": "2025-01-07T14:33:52+0100",
                    "value": "{\"level\":\"ERROR\",\"conditions\":[{\"metric\":\"new_reliability_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_security_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_maintainability_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_coverage\",\"op\":\"LT\",\"period\":1,\"error\":\"80\",\"actual\":\"0.0\",\"level\":\"ERROR\"},{\"metric\":\"new_duplicated_lines_density\",\"op\":\"GT\",\"period\":1,\"error\":\"3\",\"actual\":\"0.010933675111918313\",\"level\":\"OK\"},{\"metric\":\"new_blocker_violations\",\"op\":\"GT\",\"period\":1,\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"},{\"metric\":\"new_line_coverage\",\"op\":\"LT\",\"period\":1,\"error\":\"50\",\"actual\":\"0.0\",\"level\":\"ERROR\"},{\"metric\":\"test_errors\",\"op\":\"GT\",\"error\":\"0\",\"actual\":\"0\",\"level\":\"OK\"}],\"ignoredConditions\":false}"
                },
                {
                    "date": "2025-01-09T13:42:14+0100",
                    "value": "{\"level\":\"ERROR\",\"conditions\":[{\"metric\":\"new_reliability_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_security_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_maintainability_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_coverage\",\"op\":\"LT\",\"period\":1,\"error\":\"80\",\"actual\":\"0.0\",\"level\":\"ERROR\"},{\"metric\":\"new_duplicated_lines_density\",\"op\":\"GT\",\"period\":1,\"error\":\"3\",\"actual\":\"0.01070358213215356\",\"level\":\"OK\"},{\"metric\":\"new_blocker_violations\",\"op\":\"GT\",\"period\":1,\"error\":\"0\",\"actual\":\"4\",\"level\":\"ERROR\"},{\"metric\":\"new_line_coverage\",\"op\":\"LT\",\"period\":1,\"error\":\"50\",\"actual\":\"0.0\",\"level\":\"ERROR\"}],\"ignoredConditions\":false}"
                },
                {
                    "date": "2025-01-31T11:04:53+0100",
                    "value": "{\"level\":\"ERROR\",\"conditions\":[{\"metric\":\"new_security_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_coverage\",\"op\":\"LT\",\"period\":1,\"error\":\"80\",\"actual\":\"0.0\",\"level\":\"ERROR\"},{\"metric\":\"new_blocker_violations\",\"op\":\"GT\",\"period\":1,\"error\":\"0\",\"actual\":\"4\",\"level\":\"ERROR\"}],\"ignoredConditions\":false}"
                },
                {
                    "date": "2025-01-31T14:17:58+0100",
                    "value": "{\"level\":\"ERROR\",\"conditions\":[{\"metric\":\"new_security_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_coverage\",\"op\":\"LT\",\"period\":1,\"error\":\"80\",\"actual\":\"0.0\",\"level\":\"ERROR\"},{\"metric\":\"new_blocker_violations\",\"op\":\"GT\",\"period\":1,\"error\":\"0\",\"actual\":\"4\",\"level\":\"ERROR\"}],\"ignoredConditions\":false}"
                },
                {
                    "date": "2025-01-31T14:45:31+0100",
                    "value": "{\"level\":\"ERROR\",\"conditions\":[{\"metric\":\"new_security_rating\",\"op\":\"GT\",\"period\":1,\"error\":\"1\",\"actual\":\"1\",\"level\":\"OK\"},{\"metric\":\"new_coverage\",\"op\":\"LT\",\"period\":1,\"error\":\"80\",\"actual\":\"0.0\",\"level\":\"ERROR\"},{\"metric\":\"new_blocker_violations\",\"op\":\"GT\",\"period\":1,\"error\":\"0\",\"actual\":\"4\",\"level\":\"ERROR\"}],\"ignoredConditions\":false}"
                }
            ]
        }
    ]
}

The first probleme is that a json string is nested in the response which make the whole thing a bit hard to work with.The field I need is the “level” field in the nested json string.I’ve actually got a jsonata script to work in the jsonata playground:

{
    "value":($count(measures.history[$eval(value).level = "OK"]) /
    $count(measures.history))
}

But it does not work at all with the plugin. The eval function does not seem to work.
I also got it working with a lot of transformation but its a bit junk (I did 6 transformation..) and it only works where there are “OK” and “ERROR” in the data, if there is only “OK” or only “ERROR” it does not work.

If you could help me with this that would be great. If you need any other detail or example, let me know.

2

I like to use UQL personally

image
image994×651 24.1 KB

3

Thank you. it worked !

For future reference I also changed the jsonata to a more readable format (inspired by what I could read from the picture @yosiasz sent)

( 
    $history_count := $count(measures.history);
    $ok_count := $count(measures.history[$eval(value).level = "OK"]);
    $ok_count / $history_count
)
1 Like
4

remember math classes when we were kids! teacher said “break the math down” :laughing:

1 Like