Скачать с ютуб How to read JSON in SQL Server в хорошем качестве

How to read JSON in SQL Server

In this tutorial I will show you how to parse a JSON object in any SQL Server version that supports native JSON parsing. Below is an example with all the things that you need to know: ------------------------------------------------------------------------------------------------------------- --JSON Object DECLARE @JSON_DATA NVARCHAR (MAX); SET @JSON_DATA = '{ "squadName": "Super hero squad", "homeTown": "Metro City", "formed": 2016, "secretBase": "Super tower", "active": true, "members": [ { "name": "Molecule Man", "age": 29, "secretIdentity": "Dan Jukes", "powers": [ "Radiation resistance", "Turning tiny", "Radiation blast" ] }, { "name": "Madame Uppercut", "age": 39, "secretIdentity": "Jane Wilson", "powers": [ "Million tonne punch", "Damage resistance", "Superhuman reflexes" ] }, { "name": "Eternal Flame", "age": 1000000, "secretIdentity": "Unknown", "powers": [ "Immortality", "Heat Immunity", "Inferno", "Teleportation", "Interdimensional travel" ] } ] }'; -- Create table CREATE TABLE [dbo].[JSON_Tutorial] ( [id] [int] NOT NULL, [Squad] [nvarchar](max) NULL ); -- Add JSON object INSERT INTO [dbo].[JSON_Tutorial] VALUES(1,@JSON_DATA); --Validate JSON and get Squad name SELECT id AS SquadId, JSON_VALUE([Squad],'$.squadName') AS SquadName FROM [dbo].[JSON_Tutorial] WHERE ISJSON([Squad]) = 1 -- Parse List of Objects SELECT id AS SquadId, JSON_VALUE([Squad],'$.squadName') AS SquadName, MemeberList.MemberName, MemeberList.Age FROM [dbo].[JSON_Tutorial] CROSS APPLY OPENJSON([Squad], '$.members') WITH( MemberName NVARCHAR(100) '$.name', Age int '$.age' ) AS MemeberList WHERE ISJSON([Squad]) = 1 -- Parse array of strings SELECT id, JSON_VALUE([Squad],'$.squadName') AS SquadName, MemeberList.MemberName, MemeberList.Age, PowerData.PowerName FROM [dbo].[JSON_Tutorial] CROSS APPLY OPENJSON([Squad], '$.members') WITH( MemberName NVARCHAR(100) '$.name', Age int '$.age', MemberPower NVARCHAR(MAX) '$.powers' AS JSON ) AS MemeberList CROSS APPLY OPENJSON(MemberPower) WITH( PowerName NVARCHAR(100) '$' ) AS PowerData WHERE ISJSON([Squad]) = 1