在JSON对象中包含不存入数据库的数据是否为最佳实践？

Alright, let's walk through how to build that nested JSON response for your claims API so you can cut down on redundant API calls. Here's a step-by-step approach tailored to your setup:

1. 数据库层：一次性拉取关联数据 #

First, you'll want to avoid multiple round-trips to your SQL database by fetching all related data in a single query. Assuming your schema has these core tables:

• customers (customerNumber, customerName, ...)
• orders (orderNumber, customerNumber, ...)
• claims (claimId, orderNumber, ...)
• claim_items (claimId, articleNumber, articleName, ...)

Use a JOIN query to pull all necessary data at once:

SELECT 
  c.customerNumber, c.customerName,
  o.orderNumber,
  ci.articleNumber, ci.articleName, ci.quantity, ci.damageReason
FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber
JOIN claims cl ON o.orderNumber = cl.orderNumber
JOIN claim_items ci ON cl.claimId = ci.claimId

This gives you a flat result set that you can transform into the nested JSON structure later.

2. API层：组装嵌套响应结构 #

Next, you'll need to map that flat database output into the nested JSON format you want. The easiest way to do this is with a DTO (Data Transfer Object) to define your response shape, then group the data in your backend service.

Example (Java Spring Boot) #

First, create a DTO to match your desired JSON structure:

public class OrderClaimResponse {
    private String customerNumber;
    private String customerName;
    private String orderNumber;
    private List<ClaimItem> claims;

    // Getters, setters, and constructor
    public static class ClaimItem {
        private String articleNumber;
        private String articleName;
        private Integer quantity;
        private String damageReason;
        // Add other claim fields as needed
    }
}

Then, in your service layer, group the flat query results into the nested structure:

public List<OrderClaimResponse> getAllClaimsWithFullDetails() {
    List<Object[]> rawQueryResults = claimRepository.fetchAllClaimsWithRelations(); // Runs the JOIN query above
    Map<String, OrderClaimResponse> orderClaimMap = new HashMap<>();

    for (Object[] row : rawQueryResults) {
        String orderNumber = (String) row[2];
        OrderClaimResponse response = orderClaimMap.getOrDefault(orderNumber, new OrderClaimResponse());

        // Set customer/order data once per order
        if (response.getCustomerNumber() == null) {
            response.setCustomerNumber((String) row[0]);
            response.setCustomerName((String) row[1]);
            response.setOrderNumber(orderNumber);
            response.setClaims(new ArrayList<>());
        }

        // Add individual claim items
        OrderClaimResponse.ClaimItem item = new OrderClaimResponse.ClaimItem();
        item.setArticleNumber((String) row[3]);
        item.setArticleName((String) row[4]);
        item.setQuantity((Integer) row[5]);
        item.setDamageReason((String) row[6]);

        response.getClaims().add(item);
        orderClaimMap.put(orderNumber, response);
    }

    return new ArrayList<>(orderClaimMap.values());
}

Final JSON Output #

Your /claims endpoint will now return exactly the structure you're looking for:

[
  {
    "customerNumber": "112235-12312",
    "customerName": "A name",
    "orderNumber": "6575-12316",
    "claims": [
      {
        "articleNumber": "24141241",
        "articleName": "Article Name",
        "quantity": 1,
        "damageReason": "Shipping damage"
      }
    ]
  },
  // More order-claim objects
]

3. Extra Optimizations #

• Pagination: If you have a large number of claims, add pagination parameters (like ?page=1&size=20) to avoid overwhelming the client or server.
• Caching: Cache static customer/order data (e.g., with Redis) to reduce repeated database queries for frequently accessed records.
• Field Filtering: Let clients request only the fields they need (e.g., ?fields=customerName,claims.articleName) to keep response sizes small.

内容的提问来源于stack exchange，提问作者Pizzicato