Group by fields in CSV

InsightConnect
3

You can do this with the jq plugin.

here’s a simplified example:
input json

{"json":[
  {"field1": "john", "field2": "home", "field3": "5551210"},
  {"field1": "john", "field2": "work", "field3": "5551211"},
  {"field1": "john", "field2": "mobile", "field3": "5551212"},
  {"field1": "jane", "field2": "home", "field3": "5551213"},
  {"field1": "jane", "field2": "mobile", "field3": "5551214"},
  {"field1": "jane", "field2": "work", "field3": "5551215"}
]}

jq:
{"numbers":[.json|group_by(.field1)|.[]|map({(.field2): .field3, name: .field1})|add]}

results:

{
  "numbers": [
    {
      "home": "5551213",
      "name": "jane",
      "mobile": "5551214",
      "work": "5551215"
    },
    {
      "home": "5551210",
      "name": "john",
      "work": "5551211",
      "mobile": "5551212"
    }
  ]
}

In ICON, you’d feed the output (the entire top-level object, not the array) form the csv plugin’s to json action into the jq step. What you’ll get is a string which you then run through the typeconverter string to object action to have usable JSON in your workflow.

JQ is part templating language, part filtering language, and part query language. The above jq defines the output structure {"numbers":[ .......... ]} and then fills-in the … bit with an array using the query.
The query starts with changing context from the top level to the .json array, then it groups the entries into separate arrays using field1. With our sample input, it creates two arrays. We then iterate over these arrays (.[]) and do some mapping. The add at the end tells it to add the mapped fields to the object instead of creating new objects.

2 Likes