Need Count of values in multivalued field, grouped by another field in json data

[rajeshku348]

hi everyone,
I need count of "id" field against the sequence field

parentRecord sequence count(id)

ABC162856 2 1
ABC162856 38 4

I am using spath to extract the multivalued field but it clubs all "id" fields together, so i am unable to group by "sequence" field. I have tried extracting via REX as well but not been able to make it work. I have searched Q&A but couldn't find a similar query.

...
| spath input=jsonpayload output=ids path=data.relationships.elements.data{}.records.data{}.id
| spath input=jsonpayload output=parentID path=data.parentRecord
| spath input=jsonpayload output=sequence path=data.relationships.elements.data{}.sequence
| table parentID, sequence
| stats count(ids) by parentRecord, sequence

my query's result:

sequence count(ids)

2 5
38 5

data:

{
"data": {
"relationships": {
"elements": {
"data": [
{
"sequence": "2",
"records": {
"data": [
{
"id": "ABC62856"
}
]
},
},
{
"sequence": "38",
"records": {
"data": [
{
"id": "ABC9247022"
},
{
"id": "ABC161214"
},
{
"id": "ABC162549"
},
{
"id": "ABC162679"
},
]
},
}
]
}
},
"parentRecord": "ABC162856"
}
}

[poete]

Hello @rajeshku348,

I found a few errors in the json, and fixed it.

Here is the solution to your problem.

| makeresults 
| eval _raw="{\"data\": {\"relationships\": {\"elements\": {\"data\": [{\"sequence\": \"2\",\"records\": {\"data\": [{\"id\": \"ABC62856\"}]}},{\"sequence\": \"38\",\"records\": {\"data\": [{\"id\": \"ABC9247022\"},{\"id\": \"ABC161214\"},{\"id\": \"ABC162549\"},{\"id\": \"ABC162679\"}]}}]}},\"parentRecord\": \"ABC162856\"}}"
| spath path=data.relationships.elements.data{}
| rename data.relationships.elements.data{} as datas
| mvexpand datas
| spath input=datas
| rename records.data{}.id as id
| table sequence,id

[rajeshku348]

I was able to do it as below but I guess there must be some better way of doing this using spath directly instead of having to use rex

| rex field=_raw max_match=200 "records\": {\"data\": [(?.*?)]},"
| spath input=jsonpayload output=parentRecord path=data.id
| eval z=mvzip(sequence1,ids)
| mvexpand z
| makemv z delim=","
| eval sequence = mvindex(z,0)
| eval recordCount = mvcount(z) -1
| table _time, parentRecord , sequence, recordCount