Convert CSV Data into Multilevel JSON

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:

A

1

=file("csv.csv").import@ct()

2

=A1.group(metric;~.([value,date]):datapoints)

3

=json(A2)

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.

Q & A Collection

https://stackoverflow.com/questions/61369904/how-to-read-a-csv-to-a-nested-json-with-jackson-java