25 JSON Design for Tabular Data in REST APIs
25.1 Understanding the Context
When designing JSON for tabular data in REST APIs, you need to consider several factors: - Single row vs multiple rows - Data types and validation - Relationships between entities - API versioning - Client consumption patterns
Let me walk you through the best practices with examples.
25.2 Basic Structure for a Single Row
For a single row of data, you typically use a flat JSON object. Let’s say we have patient examination data:
{
"id": "exam-001",
"patientId": "PAT-12345",
"patientName": "John Doe",
"modality": "CT",
"examDate": "2024-01-15T14:30:00Z",
"bodyPart": "CHEST",
"status": "COMPLETED",
"radiologistId": "RAD-789",
"findings": "No acute findings"
}25.3 Best Practices for Field Design
25.3.1 1. Use Consistent Naming Conventions
{
// Good - camelCase (JavaScript/Java standard)
"examDate": "2024-01-15",
"patientName": "John Doe",
// Good - snake_case (Python/Ruby standard)
"exam_date": "2024-01-15",
"patient_name": "John Doe",
// Avoid mixing conventions
"exam_date": "2024-01-15", // snake_case
"patientName": "John Doe" // camelCase - DON'T MIX!
}25.3.2 2. Use ISO 8601 for Dates
{
"createdAt": "2024-01-15T14:30:00Z", // UTC time
"scheduledAt": "2024-01-16T09:00:00+07:00" // Bangkok timezone
}25.3.3 3. Handle Null Values Appropriately
{
"id": "exam-001",
"findings": null, // Explicitly null (no findings yet)
"reportUrl": null, // Not available
// "archived": missing // Optional field - omit if not needed
}25.4 Nested vs Flat Structure
Decision Tree for Structure Choice:
[Data Structure?]
|
---------------
| |
[Simple] [Complex]
| |
Flat Evaluate
Nesting
|
-----------------
| |
[1-to-1] [1-to-Many]
| |
Can Flatten Must Nest25.4.1 Flat Structure (Simple Cases)
{
"examId": "exam-001",
"patientId": "PAT-12345",
"patientName": "John Doe",
"patientAge": 45,
"radiologistId": "RAD-789",
"radiologistName": "Dr. Smith"
}25.4.2 Nested Structure (Complex Relationships)
{
"id": "exam-001",
"patient": {
"id": "PAT-12345",
"name": "John Doe",
"age": 45,
"gender": "M"
},
"radiologist": {
"id": "RAD-789",
"name": "Dr. Smith",
"specialization": "Chest Radiology"
},
"images": [
{
"id": "img-001",
"seriesNumber": 1,
"sopInstanceUID": "1.2.840.113619.2.1.1.1",
"url": "/api/images/img-001"
},
{
"id": "img-002",
"seriesNumber": 2,
"sopInstanceUID": "1.2.840.113619.2.1.1.2",
"url": "/api/images/img-002"
}
]
}25.5 Metadata Pattern
Include metadata for better API usability:
{
"data": {
"id": "exam-001",
"patientId": "PAT-12345",
"modality": "CT",
"examDate": "2024-01-15T14:30:00Z"
},
"meta": {
"version": "1.0",
"timestamp": "2024-01-15T16:45:00Z",
"requestId": "req-abc123"
},
"links": {
"self": "/api/exams/exam-001",
"patient": "/api/patients/PAT-12345",
"images": "/api/exams/exam-001/images",
"report": "/api/exams/exam-001/report"
}
}25.7 Validation and Data Types
{
"id": "exam-001", // string: unique identifier
"sequenceNumber": 1234, // integer: no quotes
"price": 2500.50, // number: decimal
"isUrgent": true, // boolean: no quotes
"tags": ["chest", "routine"], // array
"measurements": { // object
"lungVolume": 4.5,
"unit": "liters"
},
"notes": null // explicit null
}25.8 Error Response Pattern
When validation fails:
{
"error": {
"code": "VALIDATION_ERROR",
"message": "Invalid input data",
"details": [
{
"field": "examDate",
"issue": "Invalid date format",
"expected": "ISO 8601 format (YYYY-MM-DDTHH:mm:ssZ)"
},
{
"field": "modality",
"issue": "Invalid value",
"expected": "One of: CT, MR, CR, DX, US"
}
]
},
"meta": {
"timestamp": "2024-01-15T16:45:00Z",
"requestId": "req-xyz789"
}
}25.9 Practical Example: Medical Imaging Study
Here’s a complete example for a radiology study:
{
"data": {
"studyInstanceUID": "1.2.840.113619.2.55.3.604688",
"accessionNumber": "ACC-2024-001234",
"patient": {
"mrn": "12345678",
"name": "Doe^John^Middle",
"birthDate": "1979-05-15",
"sex": "M"
},
"study": {
"date": "2024-01-15",
"time": "14:30:45",
"description": "CT CHEST W/O CONTRAST",
"modality": "CT",
"bodyPartsExamined": "CHEST",
"institutionName": "Ramathibodi Hospital"
},
"series": [
{
"seriesInstanceUID": "1.2.840.113619.2.55.3.604689",
"seriesNumber": 1,
"seriesDescription": "Scout",
"imageCount": 3
},
{
"seriesInstanceUID": "1.2.840.113619.2.55.3.604690",
"seriesNumber": 2,
"seriesDescription": "Axial 5mm",
"imageCount": 120
}
],
"status": {
"code": "COMPLETED",
"reportStatus": "FINAL",
"reportedAt": "2024-01-15T16:30:00Z",
"reportedBy": {
"id": "RAD-789",
"name": "Dr. Smith",
"role": "ATTENDING_RADIOLOGIST"
}
}
},
"_links": {
"self": "/api/studies/1.2.840.113619.2.55.3.604688",
"report": "/api/studies/1.2.840.113619.2.55.3.604688/report",
"images": "/api/studies/1.2.840.113619.2.55.3.604688/images",
"dicom": "/api/studies/1.2.840.113619.2.55.3.604688/dicom"
},
"meta": {
"version": "2.0",
"generated": "2024-01-15T17:00:00Z"
}
}25.10 Key Takeaways
- Consistency is crucial - Pick a naming convention and stick to it
- Use proper data types - Numbers without quotes, booleans as true/false
- ISO 8601 for dates - Always use standard date formats
- Include metadata - Version, timestamps, request IDs help with debugging
- HATEOAS links - Help clients navigate your API
- Nest thoughtfully - Only when representing actual relationships
- Handle nulls explicitly - Differentiate between null and missing
- Version your API - Include version in metadata or URL