Splunk process jSON and convert the json fields to columns and values

Question

i have the following JSON which i need to convert in to a Splunk Table

"branch": "test",
"measures": {
    "component": {
        "key": "XXXXXXX",
        "measures": [{
                "metric": "alert_status",
                "value": "ERROR"
            }, {
                "bestValue": "false",
                "metric": "sqale_index",
                "value": 4652
            }, {
                "metric": "new_maintainability_rating",
                "period": {
                    "bestValue": "true",
                    "index": 1,
                    "value": 1.0
                }
            }, {
                "bestValue": "false",
                "metric": "reliability_rating",
                "value": 5.0
            }, {
                "metric": "new_security_review_rating",
                "period": {
                    "bestValue": "true",
                    "index": 1,
                    "value": 1.0
                }
            }, {
                "metric": "new_bugs",
                "period": {
                    "bestValue": "false",
                    "index": 1,
                    "value": 3
                }
            }, {
                "bestValue": "false",
                "metric": "duplicated_lines_density",
                "value": 49.7
            }, {
                "metric": "new_security_rating",
                "period": {
                    "bestValue": "true",
                    "index": 1,
                    "value": 1.0
                }
            }, {
                "metric": "new_code_smells",
                "period": {
                    "bestValue": "false",
                    "index": 1,
                    "value": 63
                }
            }
        ]
    }
}

I need to convert the data to in a table like below, where every metric need to be my column name and every corresponding value need to be my data field

I tried something liek this and it doesnt work

index=sonar_dev sourcetype="sonarqube:branch:metrics"
|rename  measures.component.name as ProjectName
|rename  branch as Branchname
|rename qualityGateStatus as QualityGate_Status 
|spath 
|rename measures.metrics{}.name as Metrics
|stats values(Branchname) by Metrics
|transpose

Answer 1

I did this one out of curiosity, however PLEASE provide a working JSON next time, since correcting the format took half the time.

That said, here is a run-anywhere SPL, with inline comments:

| makeresults ```start mock data```
    format=json 
    data="
        [
{
\"branch\": \"test\",
\"measures\": {
    \"component\": {
        \"key\": \"XXXXXXX\",
        \"measures\": [{
                \"metric\": \"alert_status\",
                \"value\": \"ERROR\"
            },{
                \"bestValue\": \"false\",
                \"metric\": \"sqale_index\",
                \"value\": 4652
            },{
                \"metric\": \"new_maintainability_rating\",
                \"period\": {
                    \"bestValue\": \"true\",
                    \"index\": 1,
                    \"value\": 1.0
                }
            },{
                \"bestValue\": \"false\",
                \"metric\": \"reliability_rating\",
                \"value\": 5.0
            },{
                \"metric\": \"new_security_review_rating\",
                \"period\": {
                    \"bestValue\": \"true\",
                    \"index\": 1,
                    \"value\": 1.0
                }
            },{
                \"metric\": \"new_bugs\",
                \"period\": {
                    \"bestValue\": \"false\",
                    \"index\": 1,
                    \"value\": 3
                }
            },{
                \"bestValue\": \"false\",
                \"metric\": \"duplicated_lines_density\",
                \"value\": 49.7
            },{
                \"metric\": \"new_security_rating\",
                \"period\": {
                    \"bestValue\": \"true\",
                    \"index\": 1,
                    \"value\": 1.0
                }
            },{
                \"metric\": \"new_code_smells\",
                \"period\": {
                    \"bestValue\": \"false\",
                    \"index\": 1,
                    \"value\": 63
                }
            }
        ]
    }
}
},
{
\"branch\": \"dev\",
\"measures\": {
    \"component\": {
        \"key\": \"XXXXXXX\",
        \"measures\": [{
                \"metric\": \"alert_status\",
                \"value\": \"WARN\"
            },{
                \"bestValue\": \"false\",
                \"metric\": \"sqale_index\",
                \"value\": 2222
            },{
                \"metric\": \"new_maintainability_rating\",
                \"period\": {
                    \"bestValue\": \"true\",
                    \"index\": 1,
                    \"value\": 2.0
                }
            },{
                \"bestValue\": \"false\",
                \"metric\": \"reliability_rating\",
                \"value\": 22.0
            },{
                \"metric\": \"new_security_review_rating\",
                \"period\": {
                    \"bestValue\": \"true\",
                    \"index\": 1,
                    \"value\": 222.0
                }
            },{
                \"metric\": \"new_bugs\",
                \"period\": {
                    \"bestValue\": \"false\",
                    \"index\": 1,
                    \"value\": 0.2
                }
            },{
                \"bestValue\": \"false\",
                \"metric\": \"duplicated_lines_density\",
                \"value\": 22.222
            },{
                \"metric\": \"new_security_rating\",
                \"period\": {
                    \"bestValue\": \"true\",
                    \"index\": 1,
                    \"value\": 2.0
                }
            },{
                \"metric\": \"new_code_smells\",
                \"period\": {
                    \"bestValue\": \"false\",
                    \"index\": 1,
                    \"value\": 220
                }
            }
        ]
    }
}
}
        ]
    "
```extract key field```
| spath output=key path=measures.component.key
```extract the metric field to a multivalue field```
| spath output=metric path=measures.component.measures{}.metric
```extract the measures array```
| spath output=Array path=measures.component.measures{}
```use regex to extract the value fields from the nestes array```
| rex field=Array max_match=100 "\"value\":\"?(?P<value>[\w.]+)\"?"
| fields - _raw Array measures
```concatenate the two multivalue fields so they "stay in sync" after expansion
count the lenght of the MV fields to make sure that you can select the last one,
which has all values "filleddown"```
| eval 
    count=mvcount(metric),
    mvcounter=mvrange(1,count+1),
    zip=mvzip(mvzip(mvcounter,metric,"####"),value,"####")
| mvexpand zip
| rex field=zip "^(?P<mvcounter>[^#]+)####(?P<metric>[^#]+)####(?P<value>[^#]+)$"
```create new variables from metric field```
| eval {metric}=value
```keep only the last row containing all information```
| filldown
| where mvcounter=count
| fields - metric value zip count mvcounter