Microsoft SQL Server JSON in Sql Server Parse JSON text using OPENJSON function
Example
OPENJSON function parses JSON text and returns multiple outputs. Values that should be returned are specified using the paths defined in the WITH clause. If a path is not specified for some column, the column name is used as a path. This function casts returned values to the SQL types defined in the WITH clause. AS JSON option must be specified in the column definition if some object/array should be returned.
DECLARE @json NVARCHAR(100) = '{"id": 1, "user":{"name":"John"}, "skills":["C#","SQL"]}'
SELECT *
FROM OPENJSON (@json)
WITH(Id int '$.id',
Name nvarchar(100) '$.user.name',
UserObject nvarchar(max) '$.user' AS JSON,
Skills nvarchar(max) '$.skills' AS JSON,
Skill0 nvarchar(20) '$.skills[0]')
Result
| Id | Name | UserObject | Skills | Skill0 |
|---|---|---|---|---|
| 1 | John | {"name":"John"} | ["C#","SQL"] | C# |
