Скачать или смотреть How to Remove Elements from a JSON Array in SQL Server

A comprehensive guide on how to remove unwanted elements from a JSON array in SQL Server. Learn the best practices and techniques for filtering JSON data efficiently.
---
This video is based on the question https://stackoverflow.com/q/64742180/ asked by the user 'John McDonnell' ( https://stackoverflow.com/u/612072/ ) and on the answer https://stackoverflow.com/a/64743399/ provided by the user 'David Browne - Microsoft' ( https://stackoverflow.com/u/7297700/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Remove elements from a JSON array, with no key

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Remove Elements from a JSON Array in SQL Server

Working with JSON data in SQL Server is common, but sometimes you might find yourself needing to manipulate that data, particularly when it comes to removing elements under certain conditions. This post will guide you through the process of efficiently removing unwanted elements from a JSON array without causing future errors in your SQL queries.

The Problem

Imagine you have a JSON array that includes different fields, but you want to filter it down based on specific criteria. For example, you want to remove any JSON elements where:

The Action is 2

The ValueBefore and ValueAfter fields are the same

Here’s an example JSON array before cleaning:

[[See Video to Reveal this Text or Code Snippet]]

You want to manipulate this JSON such that only certain elements remain, resulting in something like:

[[See Video to Reveal this Text or Code Snippet]]

This means removing elements where the ValueBefore is equal to ValueAfter and Action is 2.

The Solution

1. Initial Setup

You won't need to use JSON_MODIFY. Instead, begin by selecting the data you want in a tabular manner, filtering as you go, then re-encoding the remaining data back into JSON format.

Here's how you can do this in SQL Server:

[[See Video to Reveal this Text or Code Snippet]]

This sets up your JSON data in an NVARCHAR variable.

2. Using OPENJSON for Filtering

The next step involves using OPENJSON to parse the original JSON string, allowing you to work with it as if it were a table. Here, you can filter out unwanted elements based on the specified criteria:

[[See Video to Reveal this Text or Code Snippet]]

3. What This Code Does:

The OPENJSON function retrieves the elements of the JSON array.

The WITH clause maps the fields from the JSON array to SQL Server data types.

The WHERE clause filters out the rows meeting the criteria (i.e., where Action is 2 and ValueBefore equals ValueAfter).

Finally, FOR JSON PATH re-encodes the filtered result back into a valid JSON format.

4. Expected Output:

When you run the above SQL code, your filtered JSON will look like this:

[[See Video to Reveal this Text or Code Snippet]]

This efficient filtering leaves only the JSON elements that meet your specified criteria, paving the way for effective data management in your applications.

Conclusion

By leveraging the power of SQL Server's JSON functionalities, filtering elements from a JSON array can be accomplished without the complexity often associated with JSON path syntax. Utilizing OPENJSON, WITH, and conditional filtering, you can efficiently manage JSON data to retain only what you need. This not only streamlines your database operations but also enhances performance in handling JSON data effectively.

Feel free to reach out with any questions or share your experiences working with JSON in SQL Server in the comments below!