Read JSON in MS Sql

• Language:: T-SQL
• Type:: Back-end
• Context:: Read JSON from SQL
• Description – a text-based description of the snippet
• Snippet

declare
@JSON nvarchar(max) = '[1,2,3,4]',
@JSON2 nvarchar(max) = '[1,2,0,0]'
 
-- SELECT *, value
-- FROM OPENJSON(@JSON)
 
-- select *, value from OPENJSON(@JSON2)
 
SELECT * FROM openjson(@json) where value not in (select value from openjson(@json2))

Using Cross Apply on Multiple Json Objects

-- Sample table with JSON data
CREATE TABLE Orders (
   OrderID INT,
   CustomerName NVARCHAR(50),
   OrderDetails NVARCHAR(MAX)
);
-- Insert sample JSON data
INSERT INTO Orders (OrderID, CustomerName, OrderDetails)
VALUES
(1, 'John Doe', '[{"ProductID": 101, "ProductName": "Laptop", "Quantity": 2}, {"ProductID": 102, "ProductName": "Mouse", "Quantity": 5}]'),
(2, 'Jane Smith', '[{"ProductID": 103, "ProductName": "Keyboard", "Quantity": 1}, {"ProductID": 104, "ProductName": "Monitor", "Quantity": 2}]');
-- Query to extract JSON data into a tabular format
SELECT
   o.OrderID,
   o.CustomerName,
   od.ProductID,
   od.ProductName,
   od.Quantity
FROM Orders o
CROSS APPLY OPENJSON(o.OrderDetails)
WITH (
   ProductID INT '$.ProductID',
   ProductName NVARCHAR(50) '$.ProductName',
   Quantity INT '$.Quantity'
) od;

• Dependencies::

---

📇Additional Metadata

• 📁Type:: snippet
• 🏷️Tags:: MS Sql T-SQL
• 📡Status:: #status/🌲