Transformation Middleware for jsonColumns

[ganeshC-Movic]

Consider Adding a Middleware Layer

In your API service, consider adding a middleware layer that transforms string fields into parsed JSON objects before sending the response to the client. Adding native support for such functionality would add great value.

---

Configure DAB to Parse JSON Columns

Locate Your DAB Configuration File

This is typically named something like dabconfig.json (or similar) where you define your entities and their mappings.

Add the jsonColumns Property

Under the configuration for your stored procedure (or entity), add the names of the columns that contain JSON.

Example:

{
  "entities": [
    {
      "name": "YourStoredProcEntity",
      "entityType": "stored-procedure",
      "sqlObject": "YourStoredProcName",
      "operations": ["Read"],
      "read": {
        "default": {
          "jsonColumns": ["EventsDataJSON", "EventsData"]
        }
      }
    }
  ]
}

Explanation:

• "YourStoredProcEntity" is the name you use to reference this entity in your API.
• "YourStoredProcName" is the actual name of the stored procedure in your database.
• The "jsonColumns" array lists the column names that contain JSON. DAB will parse these strings into JSON objects (or arrays) in the final API response.

Restart or Redeploy Your API

After updating your configuration, restart or redeploy your Data API builder instance so that the changes take effect.

---

What to Expect

Original API Response (Before Parsing):

{
  "value": [
    {
      "ID": 11998,
      "Name": "Chelsea Handler",
      "EventsDataJSON": "[{\"EventID\":131183,\"EventDatum\":\"2025-05-17T00:00:00\"},{\"EventID\":131182,\"EventDatum\":\"2025-05-18T00:00:00\"}]",
      "EventsData": "{\"Events\":[{\"EventID\":131183,\"EventDatum\":\"2025-05-17T00:00:00\"},{\"EventID\":131182,\"EventDatum\":\"2025-05-18T00:00:00\"}]}"
    }
  ]
}

Transformed API Response (After Parsing):

{
  "value": [
    {
      "ID": 11998,
      "Name": "Chelsea Handler",
      "EventsDataJSON": [
        { "EventID": 131183, "EventDatum": "2025-05-17T00:00:00" },
        { "EventID": 131182, "EventDatum": "2025-05-18T00:00:00" }
      ],
      "EventsData": {
        "Events": [
          { "EventID": 131183, "EventDatum": "2025-05-17T00:00:00" },
          { "EventID": 131182, "EventDatum": "2025-05-18T00:00:00" }
        ]
      }
    }
  ]
}

---

Summary

• SQL Server Behavior: JSON output (using FOR JSON PATH or similar) comes as an NVARCHAR string.
• DAB Behavior: By default, DAB returns these as strings.
• Solution: Update your DAB configuration (dabconfig.json) to add a jsonColumns array for the columns that hold JSON. This instructs DAB to parse those string values into JSON objects/arrays in the API response.