Return a JSON response as CSV file from spring-boot controller

Question

In my spring-boot project, I am fetching information from an external API in a JSON format. The response is represented as follows:

{
  "id":237,
  "first_name":"LeBron",
  "last_name":"James",
  "position":"F",
  "height_feet": 6,
  "height_inches": 8,
  "weight_pounds": 250,
  "team":{
      "id":14,
      "abbreviation":"LAL",
      "city":"Los Angeles",
      "conference":"West",
      "division":"Pacific",
      "full_name":"Los Angeles Lakers",
      "name":"Lakers"
  }
}

My task is to return a CSV file from this JSON response. I was looking for some info on the internet and only was able to find a conversion of a regular JSON to CSV, but the JSON response I am getting is nested and the conversion didn't work. How can I make it happen? What should I do? Any help will be appreciated.

Vini · Accepted Answer · 2022-12-07 08:46:44Z

One way to do this is to pre-process the JSON data and transform it into a flat JSON structure. You could write your own method to do this or you can use JOLT library to do this:

Sample spec

[
  {
    "operation": "shift",
    "spec": {
      "*": "&",
      "team": {
        "id": "team-id",
        "abbreviation": "team-abbreviation",
        "city": "team-city",
        "conference": "team-conference",
        "division": "team-division",
        "full_name": "team-full-name",
        "name": "team-name"
      }
    }
  }
]

Will transform the JSON to

{
  "id" : 237,
  "first_name" : "LeBron",
  "last_name" : "James",
  "position" : "F",
  "height_feet" : 6,
  "weight_pounds" : 250,
  "team-id" : 14,
  "team-abbreviation" : "LAL",
  "team-city" : "Los Angeles",
  "team-conference" : "West",
  "team-division" : "Pacific",
  "team-full-name" : "Los Angeles Lakers",
  "team-name" : "Lakers"
}

You can read more about JOLT here - https://github.com/bazaarvoice/jolt#Demo

You can also play around and test your spec live at the Demo page they have created at http://jolt-demo.appspot.com/#inception

Edit: After reading though the docs a bit more - here is a shorter version of the spec that will achieve similar result as the one given above:

[
  {
    "operation": "shift",
    "spec": {
      "*": "&",
      "team": {
        "*": "team-&"
      }
    }
  }
]

Raymond Choi · Accepted Answer · 2022-12-31 18:52:18Z

You can use library Josson to do the job.

https://github.com/octomix/josson

Your example is an JSON object

Josson object = Josson.fromJsonString(
    "{" +
    "  \"id\":237," +
    "  \"first_name\":\"LeBron\"," +
    "  \"last_name\":\"James\"," +
    "  \"position\":\"F\"," +
    "  \"height_feet\": 6," +
    "  \"height_inches\": 8," +
    "  \"weight_pounds\": 250," +
    "  \"team\":{" +
    "      \"id\":14," +
    "      \"abbreviation\":\"LAL\"," +
    "      \"city\":\"Los Angeles\"," +
    "      \"conference\":\"West\"," +
    "      \"division\":\"Pacific\"," +
    "      \"full_name\":\"Los Angeles Lakers\"," +
    "      \"name\":\"Lakers\"" +
    "  }" +
    "}");

Transformation

String keys = object.getString("flatten('.','[%d]').keys().csv()");
System.out.println(keys);
String values = object.getString("flatten('.','[%d]').csv()");
System.out.println(values);

Output

id,first_name,last_name,position,height_feet,height_inches,weight_pounds,team.id,team.abbreviation,team.city,team.conference,team.division,team.full_name,team.name
237,LeBron,James,F,6,8,250,14,LAL,Los Angeles,West,Pacific,Los Angeles Lakers,Lakers

If the input is an JSON array

Josson array = Josson.fromJsonString(
    "[{" +
    "  \"id\":237," +
    "  \"first_name\":\"LeBron\"," +
    "  \"last_name\":\"James\"," +
    "  \"position\":\"F\"," +
    "  \"height_feet\": 6," +
    "  \"height_inches\": 8," +
    "  \"weight_pounds\": 250," +
    "  \"team\":{" +
    "      \"id\":14," +
    "      \"abbreviation\":\"LAL\"," +
    "      \"city\":\"Los Angeles\"," +
    "      \"conference\":\"West\"," +
    "      \"division\":\"Pacific\"," +
    "      \"full_name\":\"Los Angeles Lakers\"," +
    "      \"name\":\"Lakers\"" +
    "  }" +
    "}," +
    "{" +
    "  \"id\":888," +
    "  \"first_name\":\"Anthony\"," +
    "  \"last_name\":\"Davis\"," +
    "  \"position\":\"F\"," +
    "  \"height_feet\": 6," +
    "  \"height_inches\": 10," +
    "  \"weight_pounds\": 253," +
    "  \"team\":{" +
    "      \"id\":14," +
    "      \"abbreviation\":\"LAL\"," +
    "      \"city\":\"Los Angeles\"," +
    "      \"conference\":\"West\"," +
    "      \"division\":\"Pacific\"," +
    "      \"full_name\":\"Los Angeles Lakers\"," +
    "      \"name\":\"Lakers\"" +
    "  }" +
    "}]");

Transformation

String keys = array.getString("[0].flatten('.','[%d]').keys().csv()");
System.out.println(keys);
String values = array.getString("[]@.flatten('.','[%d]').csv().@join('\n')");
System.out.println(values);

Output

id,first_name,last_name,position,height_feet,height_inches,weight_pounds,team.id,team.abbreviation,team.city,team.conference,team.division,team.full_name,team.name
237,LeBron,James,F,6,8,250,14,LAL,Los Angeles,West,Pacific,Los Angeles Lakers,Lakers
888,Anthony,Davis,F,6,10,253,14,LAL,Los Angeles,West,Pacific,Los Angeles Lakers,Lakers

Collectives™ on Stack Overflow

Return a JSON response as CSV file from spring-boot controller

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related