I have a JSON with the form:
[
{
"id": "1",
"createDate": "6/10/2016 6:12:33 PM",
"reviewDate": "7/10/2016 1:00:00 AM",
"reviewedBy": "Dave Smith",
"notes": "corrected spelling error"
},
{
"id": "5",
"createDate": "8/09/2016 3:15:55 PM",
"reviewDate": "7/10/2016 1:00:00 AM",
"reviewedBy": "John Wayne",
"notes": "corrected spelling error"
},
{
"id": "5",
"createDate": "8/09/2016 3:15:55 PM",
"reviewDate": "3/12/2016 12:00:00 PM",
"reviewedBy": "Dave Smith",
"notes": "finished"
},
{
"id": "6",
"createDate": "16/10/2017 1:08:33 PM",
"reviewDate": "17/10/2017 1:00:00 AM",
"reviewedBy": "Dave Smith",
"notes": "changed location"
},
{
"id": "6",
"createDate": "16/10/2017 1:08:33 PM",
"reviewDate": "13/12/2017 12:00:00 PM",
"reviewedBy": "John Wayne",
"notes": "consider additional control measures"
},
{
"id": "6",
"createDate": "26/10/2017 1:08:33 PM",
"reviewDate": "13/01/2018 12:00:00 PM",
"reviewedBy": "Susan Port",
"notes": "finished"
}
]
I want to add an item to make it look like this:
[
{
"id": "1",
"seq": "1",
"createDate": "6/10/2016 6:12:33 PM",
"reviewDate": "7/10/2016 1:00:00 AM",
"reviewedBy": "Dave Smith",
"notes": "corrected spelling error"
},
{
"id": "5",
"seq": "1",
"createDate": "8/09/2016 3:15:55 PM",
"reviewDate": "7/10/2016 1:00:00 AM",
"reviewedBy": "John Wayne",
"notes": "corrected spelling error"
},
{
"id": "5",
"seq": "2",
"createDate": "8/09/2016 3:15:55 PM",
"reviewDate": "3/12/2016 12:00:00 PM",
"reviewedBy": "Dave Smith",
"notes": "finished"
},
{
"id": "6",
"seq": "1",
"createDate": "16/10/2017 1:08:33 PM",
"reviewDate": "17/10/2017 1:00:00 AM",
"reviewedBy": "Dave Smith",
"notes": "changed location"
},
{
"id": "6",
"seq": "2",
"createDate": "16/10/2017 1:08:33 PM",
"reviewDate": "13/12/2017 12:00:00 PM",
"reviewedBy": "John Wayne",
"notes": "consider additional control measures"
},
{
"id": "6",
"seq": "3",
"createDate": "26/10/2017 1:08:33 PM",
"reviewDate": "13/01/2018 12:00:00 PM",
"reviewedBy": "Susan Port",
"notes": "finished"
}
]
So far, I've been able to group the data by the value of "id":
[
[
{
"id": "1",
"createDate": "6/10/2016 6:12:33 PM",
"reviewDate": "7/10/2016 1:00:00 AM",
"reviewedBy": "Dave Smith",
"notes": "corrected spelling error"
}
],
[
{
"id": "5",
"createDate": "8/09/2016 3:15:55 PM",
"reviewDate": "7/10/2016 1:00:00 AM",
"reviewedBy": "John Wayne",
"notes": "corrected spelling error"
},
{
"id": "5",
"createDate": "8/09/2016 3:15:55 PM",
"reviewDate": "3/12/2016 12:00:00 PM",
"reviewedBy": "Dave Smith",
"notes": "finished"
}
],
[
{
"id": "6",
"createDate": "16/10/2017 1:08:33 PM",
"reviewDate": "17/10/2017 1:00:00 AM",
"reviewedBy": "Dave Smith",
"notes": "changed location"
},
{
"id": "6",
"createDate": "16/10/2017 1:08:33 PM",
"reviewDate": "13/12/2017 12:00:00 PM",
"reviewedBy": "John Wayne",
"notes": "consider additional control measures"
},
{
"id": "6",
"createDate": "26/10/2017 1:08:33 PM",
"reviewDate": "13/01/2018 12:00:00 PM",
"reviewedBy": "Susan Port",
"notes": "finished"
}
]
]
By using:
[
{
"operation": "shift",
"spec": {
"*": "@id[]"
}
},
{
"operation": "shift",
"spec": {
"*": {
"@": "[]"
}
}
}
]
Now, I haven't been able to figure how to calculate a seq value for each element in each array and then split each array.
I was able to do it separately using:
[
{
"operation": "shift",
"spec": {
"*": {
"id": "[&1].id",
"createDate": "[&1].createDate",
"reviewDate": "[&1].reviewDate",
"notes": "[&1].notes",
"$": "[&1].seq"
}
}
}
]
And I get:
[
{
"createDate": "16/10/2017 1:08:33 PM",
"id": "6",
"notes": "changed location",
"reviewDate": "17/10/2017 1:00:00 AM",
"seq": "0"
},
{
"createDate": "16/10/2017 1:08:33 PM",
"id": "6",
"notes": "consider additional control measures",
"reviewDate": "13/12/2017 12:00:00 PM",
"seq": "1"
},
{
"createDate": "26/10/2017 1:08:33 PM",
"id": "6",
"notes": "finished",
"reviewDate": "13/01/2018 12:00:00 PM",
"seq": "2"
}
]
Yes, you should group by the "id" values. You can retry with the following transformation :
[
{ // group by the "id" values
"operation": "shift",
"spec": {
"*": {
"*": "@1,id.&[]"
}
}
},
{ // generate seq values as ordinals 0,1,2 ... for each group
"operation": "shift",
"spec": {
"*": {
"id": {
"@": "&2.id", // to keep the "id" values
"*": { // while generating "seq" values
"$": "&3.seq_[]"
}
},
"*": "&1.&"
}
}
},
{ // flatten all of the arrays
"operation": "shift",
"spec": {
"*": {
"*": {
"*": {
"@": "&3[&1].&2"
}
}
}
}
},
{ // get rid of the objects labels
"operation": "shift",
"spec": {
"*": {
"*": ""
}
}
},
{ // increment the "sq" values by 1
"operation": "modify-overwrite-beta",
"spec": {
"*": {
"seq_": "=intSum(@(1,&),1)",
"seq": "=toString(@(1,seq_))"
}
}
},
{ // get rid of the auxiliary attribute "seq_"
"operation": "remove",
"spec": {
"*": {
"seq_": ""
}
}
}
]