Скачать или смотреть Extracting Data from JSON in SQL: How to Use JSON_VALUE to Select Parent Values

Learn how to efficiently extract values from JSON in SQL Server using built-in functions like `STRING_AGG` and `OPENJSON`.
---
This video is based on the question https://stackoverflow.com/q/63842761/ asked by the user 'Pan Markosian' ( https://stackoverflow.com/u/7751315/ ) and on the answer https://stackoverflow.com/a/66318791/ provided by the user 'Zhorov' ( https://stackoverflow.com/u/6578080/ ) 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: SQL JSON_VALUE select parent values

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.
---
Extracting Data from JSON in SQL: How to Use JSON_VALUE to Select Parent Values

Working with JSON data in SQL can be daunting if you're not familiar with the built-in functions SQL Server provides. If you ever needed to extract specific values from a JSON structure, you're not alone! In this guide, we'll walk through a simple scenario where we extract values from a JSON object stored in SQL Server.

The Problem

Suppose you have the following JSON object and you want to extract two sets of data:

The names of the child elements within the "properties" section, separated by commas.

The titles of those same child elements, also separated by commas.

Given the JSON structure below:

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

The expected output would be:

From the "properties": BoxTp, boxNo

From the "title" fields: Box Type, Box Number

The Solution

To achieve this, you can leverage SQL Server's JSON functions, available from version 2016 onwards, and string aggregation introduced in version 2017.

1. Set Up the JSON Variable

First, you'll need to declare your JSON data as a variable in SQL Server:

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

2. Extract Properties Child Names

To get the names of the children in the "properties," you can use the OPENJSON function with STRING_AGG to concatenate the results:

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

3. Extract Titles

Next, to retrieve the titles of the properties, you'll need another OPENJSON call to parse the inner JSON structure:

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

4. Combine Both Queries

If you want to perform this extraction in a single query, here's how you can do it:

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

Result

Executing the combined query should give you the following output:

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

Conclusion

Utilizing SQL Server's JSON capabilities can significantly streamline data retrieval from nested structures. The combination of OPENJSON and STRING_AGG functions allows you to extract and format the data effectively. Now you're equipped with the necessary skills to handle JSON data in your SQL queries like a pro!

Feel free to present your questions or any additional scenarios you've encountered in the comments section below. Happy querying!