neerajtechnicalblogs

[neerajtechnicalblogs]

Missing Helper ‘__spreadArray’:Upgrading ‘tslib’ Required

Check out my article which is available on medium platform by following link

[neerajtechnicalblogs]

Converting Table Data to JSON with UNPIVOT and FOR JSON PATH in SQL Server

Suppose we have a table called Customers with the following data:

I can provide you some sample script for creating Customers table like below

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

City VARCHAR(50),

State VARCHAR(2),

ZipCode VARCHAR(10)

);

/* Insert statement */

INSERT INTO Customers (CustomerID, FirstName, LastName, City, State, ZipCode)

VALUES (1, ‘John’, ‘Doe’, ‘New York’, ‘NY’, ‘10001’),

(2, ‘Jane’, ‘Smith’, ‘Los Angeles’, ‘CA’, ‘90001’),

(3, ‘Bob’, ‘Johnson’, ‘Chicago’, ‘IL’, ‘60601’),

(4, ‘Alice’, ‘Williams’, ‘Houston’, ‘TX’, ‘77001’);

/* checking data/ Validate data*/

To convert this data to key-value pairs, you can use the following query:

SELECT CustomerId AS [CustomerId],

[FirstName] + ‘ ‘ + [LastName] as [CustomerName],

City AS [CustomerCity],

State AS [CustomerState],

ZipCode As [CustomerZipcode]

from Customers

For JSON Path, ROOT(‘CustomerData’)

Now i am going to explain you the query

This SQL query retrieves data from a table named Customers and formats the output as a JSON object with the key "CustomerData".

Specifically, the SELECT statement retrieves data from the Customers table, and applies the following transformations:

CustomerId AS [CustomerId] renames the CustomerId column to CustomerId in the output.

[FirstName] + ' ' + [LastName] as [CustomerName] concatenates the values of the FirstName and LastName columns with a space in between, and renames the resulting column to CustomerName in the output.

City AS [CustomerCity] renames the City column to CustomerCity in the output.

State AS [CustomerState] renames the State column to CustomerState in the output.

ZipCode As [CustomerZipcode] renames the ZipCode column to CustomerZipcode in the output.

Finally, the FOR JSON PATH, ROOT('CustomerData') clause formats the output as a JSON object with a key of "CustomerData".

This output contains an array of JSON objects, with each object representing a row from the Customers table and containing the renamed column values as key-value pairs.

Output of the above query would be like this

{ “CustomerData”: [ { “CustomerId”: 1, “CustomerName”: “John Doe”, “CustomerCity”: “New York”, “CustomerState”: “NY”, “CustomerZipcode”: “10001” }, { “CustomerId”: 2, “CustomerName”: “Jane Smith”, “CustomerCity”: “Los Angeles”, “CustomerState”: “CA”, “CustomerZipcode”: “90001” }, { “CustomerId”: 3, “CustomerName”: “Bob Johnson”, “CustomerCity”: “Chicago”, “CustomerState”: “IL”, “CustomerZipcode”: “60601” }, { “CustomerId”: 4, “CustomerName”: “Alice Williams”, “CustomerCity”: “Houston”, “CustomerState”: “TX”, “CustomerZipcode”: “77001” } ] }