JSON datatype support in SqlClient

[saurabh500]

The support for the JSON data type is in preview in Azure SQL DB https://techcommunity.microsoft.com/t5/azure-sql-blog/native-json-type-amp-json-aggregates-are-now-in-private-preview/ba-p/3830753

To enable applications to leverage JSON effectively, the SqlClient driver will require changes as well.

When it comes to the APIs of the JSON support, the fundamental APIs which are foundational are

1. Enhancement of SqlDbType to offer an enum value for JSON type. This will be offered by SqlDbType.Json enum.
2. Providing a SqlType like to work with Json data. The type here will be SqlJson.

System.Data.SqlDbType ships with dotnet/runtime, and changes to SqlDbType will be made in the runtime repo. There is an issue open in dotnet/runtime at dotnet/runtime#103925

SqlJson unlike other SqlTypes will ship in Microsoft.Data.SqlClient.

Related EF issue at dotnet/efcore#32150

APIs

The APIs that have been identified for modification are

1. SqlDataReader.GetFieldValue<string>() -> Returns a JSON string.
2. SqlDataReader.GetFieldValue<JsonDocument>() -> Returns a System.Text.JsonDocument.
3. We need to support all the above Generics for SqlDataReader.GetFieldValueAsync<T>() async equivalents as well.
4. SqlDbType.Json : This would allow the SqlParameters to be qualified as Json types.
5. SqlDataReader.GetString(int colOrdinal): This should return a JSON string.
6. SqlDataReader.GetFieldValue<byte[]>(int colOrdinal). The byte[] returned should be usable by Utf8JsonReader to create a JSON reader. This is the high performance scenario in .Net.
7. SqlDataReader.GetStream() should support a TextStream which can be serialized as a JSON text. We will need to check if the COLTYPE is JSON, then we stream the data according to the payload sent by the server.
8. SqlDataReader.GetDataTypeName(Int32) : Returns The string representing the data type of the specified column. Should return JSON in this case.
9. SqlDataReader.GetFieldType(Int32) : Returns the ‘type’ for JSON
10. SqlDataReader.GetSqlJson(Int32) : Returns a SqlJson that contains the JSON stored within the corresponding field.
11. SqlParameter will not have any new APIs exposed. The behavior of existing APIs will be enhanced, so that they can work with SqlDbType and SqlJson type.

Backward compat with server without JSON support

Backward compat of new client with old server. What happens when SqlDbType.Json/SqlJson is used with a server, which doesn't have Json support. This is pending discussions.

Decision: The client will send the new TDS type to older sql server, and the sql server will error out, and the error will be exchanged appropriately with the clients.

Technical details

1. Json support will be enabled with a feature extension negotiation, which means that the client will handshake the feature support with the server. The feature extension is versioned and will start with v1. The Feature extension identifier is 0x0D
2. Feature negotiation would mean that the server can send the Json specific type information in the metadata of the result set. The tdsType for JSON is planned to be 244
3. JSON payload will be exchanged between the client and server as VARCHAR(Max) for v1 of the Feature extension negotiated.

Dependencies

1. TDS documentation changes with the protocol changes mentioned above.
2. dotnet/runtime getting the SqlDbType enum update.
3. Migrate SqlClient builds to use dotnet 9 SDK.
4. A SQL Server with the protocol changes, which can be used for testing the client changes.
5. System.Text.Json is a dependency that is needed.

Child issues and PRs tracking this work

• API Proposal: JSON support: SqlDbTypeExtensions for SqlDbType  #2668
• API Proposal: SqlJson class design to support JSON datatype #2665
• Expose Json in SqlDbType runtime#103925
• Update to .Net 9 #2669
• Update ref assemblies for all the new APIs for Json support #2670
• Support reading JSON columns via SqlDataReader #2656
• Enhance Test Coverage for JSON datatype support #3062
• Add Feature Extension for Json Support #2773
• Implement read and write functionality for JSON #2829
• Adding SqlJson and Data write with SqlJson #2880
• Add streaming support for JSON #2882
• SqlBulkCopy support for JSON datatype #2916
• Fix SqlDataReader.IsDBNull() for json #2830

[saurabh500]

cc @roji @uc-msft @apoorvdeshmukh @deepaksa1 @imasud00 @David-Engel @cheenamalhotra

[Wraith2]

Adding GetFieldValue(Async) adds a hard dependency on the System.Text.Json library.

[roji]

@Wraith2 System.Text.Json is part of .NET in modern versions, so a package reference is only needed on old TFMs. Even for those old TFMs, a system dependency such as System.Text.Json is generally not a problem.

[Wraith2]

Yup, it's just missing from the dependencies list in the first post is all.

[roji]

@Wraith2 oh I see, thanks!