MS SQL Enhancements 2022 Update: JSON | Programming Techniques

MS SQL Enhancements 2022 Update: JSON | Programming Techniques

Microsoft recently announced JSON Enhancements in Azure SQL Database, Azure SQL Managed Instance and SQL Server 2022

The functions ISJSON, JSON_VALUE, JSON_QUERY, JSON_MODIFY will allow us to validate, query and modify JSON documents

With the public preview of JSON enhancements in Azure SQL Database, Azure SQL Managed Instance and SQL Server 2022 CTP 2.0, there are enhancements to IS_JSON function and also three new JSON functions where introduced. JSON PATH EXISTS, JSON OBJECT and JSON ARRAY.

Now IS_JSON function allows us to test if a string value contains a valid JSON object or array. The new optional parameter of ISJSON function json_type_constraint can now be used to test conformance of JSON documents to the IETF RFC 8259 specification. This capability allows you to test for strings that contain a JSON value, scalar, object, or array. This functionality is like the IS JSON predicate in the ANSI SQL standard.

The new JSON_PATH_EXISTS function allows us to test for the existence of a specific SQL or JSON path expression in a JSON document. This functionality is like the JSON_EXISTS predicate in the ANSI SQL standard.

JSON OBJECT and JSON ARRAY functions allow you to construct JSON object or array from SQL data.

For more detailed information you can check the official blog by Umachandar Jayachandran