Complete Guide to JSON Parsing in TSQL

Evolution of JSON Parsing in TSQL

Prior to SQL Server 2016, TSQL lacked native support for JSON data parsing. Developers typically relied on CLR integration or wrote complex custom parsers to handle JSON data. This approach suffered from significant performance overhead, high maintenance costs, and difficulties in ensuring data consistency and integrity.

With the release of SQL Server 2016, Microsoft introduced built-in JSON support, fundamentally changing this landscape. Developers can now use specialized JSON functions directly in TSQL to parse and process JSON data without additional dependencies or complex custom code.

Core JSON Parsing Functions Explained

JSON_VALUE Function

The JSON_VALUE function extracts scalar values from JSON strings. Its basic syntax is: JSON_VALUE(json_expression, path), where json_expression is the string containing JSON data, and path specifies the location of the value to extract within the JSON structure.

For example, extracting a name from personal information JSON:

DECLARE @json NVARCHAR(MAX) = '{"name": "John", "age": 30}'
SELECT JSON_VALUE(@json, '$.name') AS PersonName

This query returns "John" as the result.

JSON_QUERY Function

When extracting JSON objects or arrays, use the JSON_QUERY function. Unlike JSON_VALUE, JSON_QUERY returns complete JSON fragments rather than individual scalar values.

Example: Extracting an address object

DECLARE @json NVARCHAR(MAX) = '{"person": {"name": "John", "address": {"street": "Main St", "city": "NYC"}}}'
SELECT JSON_QUERY(@json, '$.person.address') AS AddressObject

OPENJSON Function

OPENJSON is the most powerful JSON parsing function, capable of converting JSON arrays or objects into relational rowsets. This enables developers to process JSON data using standard SQL query syntax.

Basic usage example:

DECLARE @json NVARCHAR(MAX) = '[{"id": 1, "name": "John"}, {"id": 2, "name": "Jane"}]'
SELECT * FROM OPENJSON(@json) WITH (id INT '$.id', name NVARCHAR(50) '$.name')

For complex nested JSON structures, combine multiple OPENJSON calls:

DECLARE @json NVARCHAR(MAX) = '[{"id": 1, "skills": ["SQL", "C#"]}, {"id": 2, "skills": ["Java", "Python"]}]'
SELECT id, skill
FROM OPENJSON(@json) WITH (id INT '$.id', skills NVARCHAR(MAX) '$.skills' AS JSON)
OUTER APPLY OPENJSON(skills) WITH (skill NVARCHAR(20) '$')

JSON_MODIFY Function

The JSON_MODIFY function allows modification of specific values within JSON documents without reconstructing the entire document. This is particularly useful when updating JSON data.

Example: Updating age field in JSON

DECLARE @json NVARCHAR(MAX) = '{"name": "John", "age": 25}'
SET @json = JSON_MODIFY(@json, '$.age', 26)
SELECT @json AS ModifiedJSON

Performance Optimization and Best Practices

Data Type Selection

SQL Server 2025 introduced native JSON data type, which offers better read performance, more efficient storage, and faster update operations compared to traditional NVARCHAR(MAX). It's recommended to use JSON data type for new projects.

Indexing Strategy

Creating computed columns for JSON properties and building indexes can significantly improve query performance:

ALTER TABLE People
ADD Age AS JSON_VALUE(JsonData, '$.age')
CREATE INDEX IX_People_Age ON People(Age)

Error Handling

Use the ISJSON function to validate JSON data integrity:

SELECT CASE WHEN ISJSON(@json) = 1 THEN 'Valid JSON' ELSE 'Invalid JSON' END AS ValidationResult

Practical Application Scenarios

REST API Integration

Modern web services commonly use JSON as data exchange format. With TSQL's JSON capabilities, API requests and responses can be processed directly at the database layer, reducing application complexity.

Log Data Analysis

Storing application logs in JSON format and using OPENJSON for querying and analysis enables quick extraction of valuable business insights.

Configuration Data Management

Using JSON columns to store application configurations allows flexible management of environment-specific or user-specific settings while maintaining data structure integrity.

Compatibility Considerations

JSON functionality requires database compatibility level 130 or higher. Ensure proper database configuration before using these features:

ALTER DATABASE CurrentDatabase SET COMPATIBILITY_LEVEL = 130

For versions prior to SQL Server 2016, custom parsers or CLR integration can still be used to handle JSON data, but upgrading to versions with native JSON support is recommended for better performance and development experience.