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"
}
}
... View more