[Enh]: Support `JSON` data type for MSSQL

[JerryNixon]

JSON data type

In SQL (Azure SQL Database and SQL Server 2025+), the JSON datatype stores structured JSON values as binary but is treated as a string for input.

Important

We will treat JSON as a string both as input and output.

CREATE TABLE profiles (
    id INT PRIMARY KEY,
    metadata JSON
)

Reading JSON

With FOR JSON, SQL emits JSON columns as objects.

SELECT id, metadata FROM profiles FOR JSON AUTO;

[
  {
    "id": 1,
    "metadata": { "role": "admin", "preferences": { "darkMode": true } }
  }
]

Data API builder (DAB) will return JSON columns as raw strings:

{
  "value": [
    {
      "id": 1,
      "metadata": "{\"role\":\"admin\",\"preferences\":{\"darkMode\":true}}"
    }
  ]
}

Writing JSON

JSON values must be passed as valid strings.

INSERT INTO profiles (id, metadata)
VALUES (
    2,
    '{"role":"guest","preferences":{"darkMode":false}}'
);

DAB input example:

POST /profiles
Content-Type: application/json

{
  "id": 2,
  "metadata": "{\"role\":\"guest\",\"preferences\":{\"darkMode\":false}}"
}

Notes

1. SQL validates JSON syntax, not DAB.
2. Neither SQL nor DAB enforces a schema.