Support JSON data type

[HoneyryderChuck]

MS SQL Server seems to have support for JSON data types. Would it be possible to support it via tinyTDS? Reported in sequel as well

[andyundso]

could be possible, but it is not straight-forward.

1. freetds needs to support it. we rely on dbcoltype which tells us the column type in MSSQL and we can select the proper Ruby type to convert the value into. usually, there is a constant defined here in the freetds docs that would describe it, but I do not see anything here that would indicate support for this new JSON type.
2. in tiny_tds, we will have to map it out. I assume you would expect tiny_tds to return a hash, right?
3. This feature seems to be in preview and for Azure only, which makes it hard for us to develop and test. I would suggest we wait until the feature is no longer considered a preview (API is stable). maybe there will be a SQL Server 2025 release where this JSON data type will be included, which would make it much easier for us.

[HoneyryderChuck]

thx for the answer! It makes sense to wait 👍

[Michoels]

For now I've been doing this by using NVACHAR(MAX) with a valid JSON check constraint:

-- assuming `my_table` with a column `metadata`
ALTER TABLE my_table
ADD CONSTRAINT my_table_metadata_must_be_json CHECK (ISJSON(metadata, OBJECT)=1);

This approach does not provide the more efficient binary storage the native JSON type provides.

[andyundso]

looks like Microsoft is working on SQL Server 2025. I would assume a release later this year and likely also includes the JSON changes.
https://www.microsoft.com/en-us/sql-server/blog/2024/11/19/announcing-microsoft-sql-server-2025-apply-for-the-preview-for-the-enterprise-ai-ready-database/