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.
