SQL Server JSON Functions

This page provides you with a comprehensive guide to the most commonly used SQL Server JSON functions, providing insights into validating JSON text, creating JSON data, querying JSON objects, and modifying JSON data.

Section 1. JSON Path Expressions #

This section focuses on navigation JSON structures using JSON paths and shows you how to check if a JSON path exists in a JSON string.

JSON Path Expressions in SQL Server – Show how to use JSON path expression to locate elements within a JSON document.
JSON_PATH_EXISTS() – Determine if a specified JSON path exists in a JSON document.

Section 2. Validating JSON #

This section demonstrates the function of validating JSON documents to ensure their integrity and correctness.

ISJSON() – Determine if a JSON string is valid, which can be useful in data validation processes.

Section 3. Constructing JSON objects and arrays #

This section introduces JSON functions for constructing JSON structures, including objects and arrays.

JSON_OBJECT() – construct JSON object text from zero or more key/value pairs.
JSON_ARRAY() – construct JSON array text from zero or more values.

Section 4. Querying JSON elements #

This section explores techniques for extracting data from JSON strings, enabling efficient retrieval of scalar values, arrays, and objects.

JSON_VALUE() – Extract a scalar value from a JSON string.
JSON_QUERY() – Extract a JSON object or array from a JSON document.
OPENJSON() – Convert a JSON document to rows and columns.

Section 5. Modifying JSON #

This section covers the function that allows you to delete, update, and insert a property into a JSON string.

JSON_MODIFY() – Modify the value in a JSON string and return the updated JSON data.
FOR JSON – Show you how to convert JSON documents to relational views.

Was this tutorial helpful?