JSON (JavaScript Object Notation) has grown in popularity as a simple and versatile data format for delivering and storing information. SQL Server's JSON data type and many built-in functions allow you to store and handle JSON data. This tutorial will teach you how to query, index, and operate with JSON documents in SQL Server.

Create one table and work with a table named "members" to store member information, including locations in JSON format
CREATE TABLE Members (
    userID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
  Location NVARCHAR(max),
    Salary NVARCHAR(50)
);

What is the best way to insert JSON data into SQL Server?
JSON data can be stored in SQL Server using the JSON data type introduced in SQL Server 2016 and later editions. JSON documents can be directly stored in columns using the JSON data type, allowing for rapid querying and processing.

INSERT INTO Members (userID, FirstName, LastName, Location,Salary)
VALUES (2, 'Peter', 'Scott', '[{"Type": "Home", "Street": "London", "City": "lucknow", "Zip": "111111"}, {"Type": "Work", "Street": "s1", "City": "Manchester", "Zip": "111111"}]',70000);

How to query JSON data in SQL Server?
Using SQL Server to Query JSON Data: SQL Server has multiple techniques for interacting with JSON data. JSON_VALUE, which extracts a scalar value from a JSON string, is one of the most frequently used functions.
SELECT userID, FirstName, LastName,
    JSON_VALUE(Location.Value, '$.Street') AS Street,
    JSON_VALUE(Location.Value, '$.City') AS City,
    JSON_VALUE(Location.Value, '$.Zip') AS Zip,Salary
FROM Members
CROSS APPLY OPENJSON(Location) AS Location
WHERE JSON_VALUE(Location.Value, '$.Type') = 'Home';

How to filter data in JSON file?
The JSON_QUERY function allows you to filter JSON data based on set parameters.

SELECT userID, JSON_QUERY(Location) AS Membername
FROM Members;

How to update JSON data in SQL Server?
SQL Server has functions to add, update, and remove properties from JSON documents.

UPDATE Members
SET Location = JSON_MODIFY(Location, '$[2].street', 'New area')
WHERE userID = 2;

How to Aggregate JSON Data in SQL Server?
The FOR JSON clause can aggregate JSON data and return result sets using JSON formatting.
SELECT FirstName, LastName
FROM Members
FOR JSON auto ;

Thanks for reading, and I hope you like it.

HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.