Forum Discussion

GanguP's avatar
GanguP
New Contributor III
2 years ago
Solved

Extract or Flatten JSON Array data as columns

Need help in to pulling data from the below JSON and also from the Array (Tags) below.   I also went through the documentation but unable to get to the solution.     "entity": { "totalRecords"...
  • koryknick's avatar
    koryknick
    2 years ago

    vgautam64 - I believe this solves your question in the simplest way I can think of.  Use a Mapper snap with the following configuration:

    It appears to me that you only need the field elements and values from the first group element: $group[0]

    The two jsonPath statements are simply pulling all values for the Debit and Credit Amounts into an array of numbers

    Finally, the Array.reduce() method is used to aggregate that list of numbers into a final total.

    Hope this helps!

     

  • Supratim's avatar
    Supratim
    Contributor III
    2 years ago
    Copy LinkReport

    vgautam64  As I can't copy your json, have created my own, you can use it- 

    Data  ==> 

    {
        "carList": [
        {
        "color": "purple",
        "type": "minivan",
        "debit_amount": 50,
        "credit_amount": 7
      },
      {
        "color": "red",
        "type": "station wagon",
        "debit_amount": 100,
        "credit_amount": 5
      }, {
        "color": "black",
        "type": "toyota",
        "debit_amount": 200,
        "credit_amount": 5
      },
      {
        "color": "blue",
        "type": "Maruti",
        "debit_amount": 300,
        "credit_amount": 5
      }
    ]
    }
     
    1. mapper  : 
    $carList.map((val,index)=>{"sum_debit_amount": jsonPath($, "carList[*].debit_amount").reduce((cur,acc)=>cur+acc),"sum_credit_amount" : jsonPath($, "carList[*].credit_amount").reduce((cur,acc)=>cur+acc)}.extend(val)).shift()
    • vgautam64's avatar
      vgautam64
      New Contributor III
      2 years ago
      Copy LinkReport

      Hi Supratim,

      Thanks for the providing a solution. I tried to validate the expression with the data I have and it seems to work when I validate the pipe. Strangely though, I observe that when I execute (not validate) the pipeline, the mapper that holds the expression only seem to process 50 records and gets stuck processing the 51st doc. But when I validate the pipe I am able to see the preview of 148 documents.

      Any idea why this would happen?

  • bojanvelevski's avatar
    bojanvelevski
    Valued Contributor
    2 years ago
    Copy LinkReport

    Hi vgautam64 ,

    Try the following expression in a mapper:

    $group.reduce((acc,curr,ind)=> acc.extend(curr.mapKeys((val,key)=>key+ind)),{})

    Let me know what you think.

    Regards,

    Bojan

  • koryknick's avatar
    koryknick
    Employee
    2 years ago
    Copy LinkReport

    vgautam64 - I believe this solves your question in the simplest way I can think of.  Use a Mapper snap with the following configuration:

    It appears to me that you only need the field elements and values from the first group element: $group[0]

    The two jsonPath statements are simply pulling all values for the Debit and Credit Amounts into an array of numbers

    Finally, the Array.reduce() method is used to aggregate that list of numbers into a final total.

    Hope this helps!