Single Jolt Spec to return Child objects and Parent objects without null or Dups

Question

I have a requirement where JSON may or may not have Array objects. My jolt specs expected JSON condition should be if it has array flatten the data and if it doesn't have array return same data

My sample input

[
  {
    "PC9Code": "Parent1",
    "PC13": [
      {
        "SKU": "Child1_1",
        "DIM1": "30",
        "DIM2": "80"
      }
    ],
    "OperationType": "UPDATE",
    "TimeStamp": "6/2/2022  4:52:17 PM"
  },
  {
    "PC9Code": "parent2",
    "ProductDescription": "94520 STANDARD SHORT STEEL BLACK ADV SHO",
    "TimeStamp": "6/2/2022  4:52:17 PM"
  },
  {
    "PC9Code": "Parent3",
    "ProductDescription": "94520 STANDARD SHORT STEEL BLACK ADV SHO",
    "PC13": [
      {
        "SKU": "Child3_1",
        "DIM1": "30",
        "DIM2": "30"
      },
      {
        "SKU": "Child3_2",
        "DIM1": "30",
        "DIM2": "80"
      }
    ],
    "OperationType": "UPDATE",
    "TimeStamp": "6/2/2022  4:52:17 PM"
  },
  {
    "PC9Code": "Parent4",
    "PC13": [
      {
        "SKU": "child4_1",
        "DIM1": "30",
        "DIM2": "30"
      },
      {
        "SKU": "Child4_2",
        "DIM1": "30",
        "DIM2": "80"
      },
      {
        "SKU": "Child4_3",
        "DIM1": "30",
        "DIM2": "80"
      }
    ]
  },
  {
    "PC9Code": "Parent5",
    "ProductDescription": "94520 STANDARD SHORT STEEL BLACK ADV SHO",
    "PC13": [
      {}
    ],
    "OperationType": "UPDATE",
    "TimeStamp": "6/2/2022  4:52:17 PM"
  },
  {
    "PC9Code": "parent6",
    "ProductDescription": "94520 STANDARD SHORT STEEL BLACK ADV SHO",
    "TimeStamp": "6/2/2022  4:52:17 PM",
    "OperationType": "UPDATE"
  },
  {
    "PC9Code": "Parent7",
    "PC13": [
      {
        "SKU": "child7_1",
        "DIM1": "30",
        "DIM2": "30"
      },
      {
        "SKU": "Child7_2",
        "DIM1": "30",
        "DIM2": "80"
      }
    ]
  }
]

It was supposed to return 11 elements.

My sample jolt spec

[
  {
    "operation": "shift",
    "spec": {
      "@": "input",
      "@(0)": "val0"
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "val0": "=toString",
      "chr": ["=substring(@(1,val0),0,1)", "["]
    }
  },
  {
    "operation": "shift",
    "spec": {
      "chr": {
        "{": {
          "@(2,input)": "[]"
        },
        "*": {
          "@(2,input)": ""
        }
      }
    }
  }, {
    "operation": "shift",
    "spec": {
      "*": {
        "PC13": {
          "*": {
            "@": "&[&3]",
            "@(2,OperationType)": "&[&3].OperationType",
            "@(2,PC9Code)": "&[&3].PC9Code",
            "@(2,TimeStamp)": "&[&3].TimeStamp"
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": "&2.&1.&" // separate each object levels
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": ""
      }
    }
  }
]

My Output: 9 elements

[
  {
    "SKU": "Child1_1",
    "DIM1": "30",
    "DIM2": "80",
    "OperationType": "UPDATE",
    "PC9Code": "Parent1",
    "TimeStamp": "6/2/2022  4:52:17 PM"
  },
  {
    "SKU": "Child3_1",
    "DIM1": "30",
    "DIM2": "30",
    "OperationType": "UPDATE",
    "PC9Code": "Parent3",
    "TimeStamp": "6/2/2022  4:52:17 PM"
  },
  {
    "SKU": "child4_1",
    "DIM1": "30",
    "DIM2": "30",
    "PC9Code": "Parent4"
  },
  {
    "OperationType": "UPDATE",
    "PC9Code": "Parent5",
    "TimeStamp": "6/2/2022  4:52:17 PM"
  },
  {
    "SKU": "child7_1",
    "DIM1": "30",
    "DIM2": "30",
    "PC9Code": "Parent7"
  },
  {
    "SKU": "Child3_2",
    "DIM1": "30",
    "DIM2": "80",
    "OperationType": "UPDATE",
    "PC9Code": "Parent3",
    "TimeStamp": "6/2/2022  4:52:17 PM"
  },
  {
    "SKU": "Child4_2",
    "DIM1": "30",
    "DIM2": "80",
    "PC9Code": "Parent4"
  },
  {
    "SKU": "Child7_2",
    "DIM1": "30",
    "DIM2": "80",
    "PC9Code": "Parent7"
  },
  {
    "SKU": "Child4_3",
    "DIM1": "30",
    "DIM2": "80",
    "PC9Code": "Parent4"
  }
]

I tried to add else condition my jolt but it is repeating my parent data again. Please help in resolving this issue.

expected output

[
  {
    "SKU": "Child1_1",
    "DIM1": "30",
    "DIM2": "80",
    "OperationType": "UPDATE",
    "PC9Code": "Parent1",
    "TimeStamp": "6/2/2022  4:52:17 PM"
  },
  {
    "SKU": "Child3_1",
    "DIM1": "30",
    "DIM2": "30",
    "OperationType": "UPDATE",
    "PC9Code": "Parent3",
    "TimeStamp": "6/2/2022  4:52:17 PM"
  },
  {
    "SKU": "child4_1",
    "DIM1": "30",
    "DIM2": "30",
    "PC9Code": "Parent4"
  },
  {
    "OperationType": "UPDATE",
    "PC9Code": "Parent5",
    "TimeStamp": "6/2/2022  4:52:17 PM"
  },
  {
    "SKU": "child7_1",
    "DIM1": "30",
    "DIM2": "30",
    "PC9Code": "Parent7"
  },
  {
    "PC9Code": "parent2",
    "TimeStamp": "6/2/2022  4:52:17 PM"
  },
  {
    "OperationType": "UPDATE",
    "PC9Code": "parent6",
    "TimeStamp": "6/2/2022  4:52:17 PM"
  },
  {
    "SKU": "Child3_2",
    "DIM1": "30",
    "DIM2": "80",
    "OperationType": "UPDATE",
    "PC9Code": "Parent3",
    "TimeStamp": "6/2/2022  4:52:17 PM"
  },
  {
    "SKU": "Child4_2",
    "DIM1": "30",
    "DIM2": "80",
    "PC9Code": "Parent4"
  },
  {
    "SKU": "Child7_2",
    "DIM1": "30",
    "DIM2": "80",
    "PC9Code": "Parent7"
  },
  {
    "SKU": "Child4_3",
    "DIM1": "30",
    "DIM2": "80",
    "PC9Code": "Parent4"
  }
]

Thanks in Advance.

Would it be possible to better format the JSON? It’s a bit hard to read — Mike R, Oct 22 '22 at 14:56

score 1 · Accepted Answer · answered Oct 24 '22 at 19:47

What you need seems to group the objects by PC9Code and PC13 tags simultaneously such as similar to the following specs which will yield the desired eleven sub-objects :

[
  {
    // expand the objects grouped by PC9Code and PC13
    "operation": "shift",
    "spec": {
      "*": {
        "PC*": {
          "*": {
            "*": "&3.&1.&",
            "@(2,PC9Code)": "&3.&1.PC9Code",
            "@(2,TimeStamp)": "&3.&1.TimeStamp",
            "@(2,OperationType)": "&3.&1.OperationType"
          }
        }
      }
    }
  },
  {
    // combine redundant sub-objects which have some common key-value pairs
    "operation": "shift",
    "spec": {
      "*": {
        "0|*arent*": {
          "*": "&2.0.&"
        },
        "*": {
          "*": "&2.&1.&"
        }
      }
    }
  },
  {
    // get rid of object tags
    "operation": "shift",
    "spec": {
      "*": {
        "*": ""
      }
    }
  },
  {
    // get rid of repeated components of sub-arrays
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": "ONE"
      }
    }
  }
]

Single Jolt Spec to return Child objects and Parent objects without null or Dups

1 Answers1