How to make nested JSON response with Array from a Stored procedure

Question

I have a data set that is generated from a Stored procedure

+------+--------+---------------------+------------+
| Line |Status  | Consent             | tid        |
+------+--------+---------------------+------------+
| 1001 | 1      | Yes                 | 1054       |
| 1002 | 1      | yes                 | 1055       |
| 1003 | 1      | Yes                 | 3045       |
| 1004 | 1      | No                  | 3046       |
| 1005 | 0      | No                  | 1023       |
| 1006 | 0      | Yes                 | 1024       |
| 1007 | 1      | No                  | 1025       |
+------+--------+---------------------+------------+

I want to be able to generate a JSON like from the data I get from the stored procedure

{
    "totalMembers": 9,
    "questionaryinfo": [
        {
            "line": "1001",
            "status": "1",
            "consent": "Yes",
            "tid": "1054",
            "adultquestionary":"Yes"
        },
        {
            "line": "1002",
            "status": "1",
            "consent": "Yes",
            "tid": "1055",
            "adultquestionary":"Yes"
        },
        {
            "line": "1003",
            "status": "1",
            "consent": "Yes",
            "tid": "3035",
            "adultquestionary":"Yes"
        },
}

I have tried using hash maps

public ResponseEntity<Map<String, Object>> retrieveLineListing(String Case){

    StoredProcedureQuery query = entityManager.createStoredProcedureQuery("get_listing", "ListingModel");
    query.registerStoredProcedureParameter("Case", String.class, ParameterMode.IN);
    query.setParameter("Case", Case);
    query.execute();

    List list = query.getResultList();

    Map<String, Object> parent = new HashMap<>();
    parent.put("totalMembers", 9);

    parent.put("questionaryinfo", new HashMap<String, Object>());
  parent.put("questionaryinfo", new ArrayList<HashMap<String,Object>>());
ArrayList<HashMap<String,Object>> listings = (ArrayList<HashMap<String,Object>>) parent.get("questionaryinfo");
if (list != null) {
    list.forEach(d -> {
        HashMap<String,Object> entry = new HashMap<>();

        entry.put("adultquestionary","Yes");
        entry.put("consent", list.get(1));
        listings.add(entry);
    });
}
    return ResponseEntity.ok().body(parent);
}

UPDATE 1

After amending to how @Rahul advised me to do,

my JSON looks like

{
    "totalMembers": 9,
    "questionaryinfo": [
      "consent"{
        {
            "line": "1001",
            "status": "1",
            "consent": "Yes",
            "tid": "1054",
          
        },
        "adultquestionary":"Yes"
      }
      "consent"{
        {
            "line": "1002",
            "status": "1",
            "consent": "Yes",
            "tid": "1055",
        },
        "adultquestionary":"Yes"
      },
      "consent"{
        {
            "line": "1003",
            "status": "1",
            "consent": "Yes",
            "tid": "3035",
        },
        "adultquestionary":"Yes"
      },
      ]
    }
    

How can I set up my method to generate the nested JSON above and get the Line, Status, consent and tid values ?

Answer 1

I would suggest you to use DTO models for your requests and responses as a better practice in your Resource/Controller layer. You will still need to populate the model correctly with the stored procedure response, but that should not be a challenge from what I can see in your post.

The real benefits of encapsulating the format of your response into a single model are flexibility, extensibility and the variety of responses you API may return.

For you to retrieve the payload defined above, it must be as simple as this:

@RestController
@RequestMapping("/v1/stackoverflow")
public class DemoController {

    @GetMapping
    public ResponseEntity<ResponseDTO> read() {
        List<Consent> result = mockStoreProcedureResult();
        ResponseDTO response = ResponseDTO.builder()
                .totalMembers(result.size())
                .questionaryInfo(result)
                .build();
        return ResponseEntity.ok(response);
    }

}

In which ResponseDTO model encapsulates the desired shape of your response. You will see that I also created a Consent model that reflects your initial proposal.

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder(toBuilder = true)
public class ResponseDTO {
    private Integer totalMembers;
    private List<Consent> questionaryInfo;
}


@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder(toBuilder = true)
public class Consent {
     private String line;
     private String status;
     private String consent;
     private String tid;
     private String adultQuestionary;
}

When I ran the demo app and make a request, I get a similar response to what you are looking for. If I wanted to alter the format of the objects inside questionaryInfo, is just as simple as editing the model being used for that.

curl -X GET --location "http://localhost:8080/v1/stackoverflow" \
    -H "Content-Type: application/json"

HTTP/1.1 200 
Content-Type: application/json
Transfer-Encoding: chunked
Date: Tue, 29 Jun 2021 18:35:11 GMT
Keep-Alive: timeout=60
Connection: keep-alive

{
  "totalMembers": 3,
  "questionaryInfo": [
    {
      "line": "1001",
      "status": "1",
      "consent": "Yes",
      "tid": "1054",
      "adultQuestionary": "Yes"
    },
    {
      "line": "1002",
      "status": "1",
      "consent": "Yes",
      "tid": "1055",
      "adultQuestionary": "Yes"
    },
    {
      "line": "1003",
      "status": "1",
      "consent": "Yes",
      "tid": "3055",
      "adultQuestionary": "Yes"
    }
  ]
}

Hope you find this useful, cheers.

Edit: Answering to your question @arriff, how do you inject values to your response model?

You could easily iterate over the list you get back from your stored procedure and map each entry into your model. For that a constructor must be defined in you model that can receive the expected parameters.

public List<Consent> getConsentAnswers() {
    return dbStoreProcedureResult().stream()
            .map(dbo -> new Consent(dbo, "Yes", "Yes"))
            .collect(Collectors.toList());
}

Here is the additional custom constructor defined for Consent model.

public Consent(@NonNull DBConsent dbo,
               @NonNull String consent,
               @NonNull String adultQuestionary) {
     this.line = dbo.getLine();
     this.tid = dbo.getTid();
     this.status = dbo.getStatus();
     this.consent = consent;
     this.adultQuestionary = adultQuestionary;
}

Answer 2

Not tried, but this should run:

parent.put("questionaryinfo", new ArrayList<HashMap<String,Object>>());
ArrayList<HashMap<String,Object>> listings = (ArrayList<HashMap<String,Object>>) parent.get("questionaryinfo");
if (list != null) {
    list.forEach(d -> {
        HashMap<String,Object> entry = new HashMap<String,Object>();
        
        entry.put("adultquestionary","Yes");
        entry.put("consent", list.get(0));
        listings.add(entry);
    });
}