csv to json conversion where some tags may not come in random

Question

I have a csv input like below: Notice that the tag Edible is not coming for the second set of values. Also notice that the data for one object is coming in columns as well as three rows:

Key|Value|ConcatenatedString
Name|Fruit|"apple,orange,pineapple"
Status|Good|"apple,orange,pineapple"
Edible|Yes|"apple,orange,pineapple"
Name|Furniture|"chair,table,bed"
Status|Good|"chair,table,bed"

I need it in the below json format:

{
Name:"Fruit",
Status:"Good",
Edible:"Yes"
ConcatenatedString:"apple,orange,pineapple"
},
{
Name:"Furniture",
Status:"Good",
Edible:null
ConcatenatedString:"chair,table,bed"
}

I was using the below code when all the tags were coming for all objects. But now that some tags may not come at all I am not sure how to handle this as I was using a position based approach:

%dw 2.0

input payload application/csv separator='|'
output application/json
---
payload map
{
Name:payload[(($$)*4)+0].Value,
Status:payload[(($$)*4)+1].Value,
Edible:payload[(($$)*4)+2].Value,
ConcatenatedString:payload[(($$)*4)+0]."ConcatenatedString"
} 
filter ($.Name!= null)

Thanks in advance,

Anoop

Answer 1

here is my answer.

%dw 2.0
input payload application/csv  separator="|"
output application/json  
---
payload 
    groupBy ((item, index) -> item.ConcatenatedString) 
    pluck ((value, key, index) -> {
        Name: (value filter ((item, index) -> item.Key == "Name")).Value[0],
        Status: (value filter ((item, index) -> item.Key == "Status")).Value[0],
        Edible: (value filter ((item, index) -> item.Key == "Edible")).Value[0],
        ConcatenatedString: key
    })

Basically first you need to group by the criteria you want to group by. In your case ConcatenatedString. This returns

{
  "chair,table,bed": [
    {
      "Key": "Name",
      "Value": "Furniture",
      "ConcatenatedString": "chair,table,bed"
    },
    {
      "Key": "Status",
      "Value": "Good",
      "ConcatenatedString": "chair,table,bed"
    }
  ],
  "apple,orange,pineapple": [
    {
      "Key": "Name",
      "Value": "Fruit",
      "ConcatenatedString": "apple,orange,pineapple"
    },
    {
      "Key": "Status",
      "Value": "Good",
      "ConcatenatedString": "apple,orange,pineapple"
    },
    {
      "Key": "Edible",
      "Value": "Yes",
      "ConcatenatedString": "apple,orange,pineapple"
    }
  ]
}

And then you iterate with pluck by every key value pair and filter the elements you want to map.