Convert CSV Data into Multilevel JSON #eg56

Problem description & analysis

Below is data in CSV file csv.csv:

metric,value,date

temp_a,622.0,1477895624866

temp_a,-3.0,1477916224866

temp_a,365.0,1477917224866

temp_b,861.0,1477895624866

temp_b,767.0,1477917224866

We are trying to convert the categorized CSV data into multilevel data, as shown below:

[
  {
    "metric":"temp_a",
    "datapoints":[
      [622, 1477895624866],
      [-3, 1477916224866],
      [365, 1477917224866]
    ]
  },
  {
    "metric":"temp_b",
    "datapoints":[
      [861, 1477895624866],
      [767, 1477917224866]
    ]
  }
]
Solution

Write the following script p1.dfx in esProc:

Explanation:

A1   Import the CSV data as a table sequence.

A2  Group A1’s data by metric field. Each group contains several pairs of [value,date], which are named datapoints.

A3  Convert the multilevel data into JSON format.

SPL open source address

Download