SQL 2016 - JSON

SELECT 
 [name]
,[collation_name]
,[compatibility_level]
,[recovery_model_desc]
FROM SYS.DATABASES

-- Visual Studio (SSDT) Pretty JSON output - CTRL+K CTRL+D 

-- FOR JSON AUTO
SELECT 
NAME AS 'DbName'
,collation_name as 'Properties.Collation'
,compatibility_level as 'Properties.CompatibilityLevel'
,recovery_model_desc as 'Properties.RecoveryModel'
FROM SYS.DATABASES
WHERE DATABASE_ID < 5
FOR JSON AUTO, ROOT ('Databases')

-- FOR JSON PATH
SELECT
NAME AS 'DbName'
,collation_name as 'Properties.Collation'
,compatibility_level as 'Properties.CompatibilityLevel'
,recovery_model_desc as 'Properties.RecoveryModel'
FROM SYS.DATABASES
WHERE DATABASE_ID < 5
FOR JSON PATH, ROOT('Databases')

-- Use nvarchar(max) as datatype for JSON strings
declare @json nvarchar(max) = N'
  {
    "DbName": "master",
    "Properties": {
      "Collation": "Latin1_General_CI_AI",
      "CompatibilityLevel": 130,
      "RecoveryModel": "SIMPLE"
    }
  }'

-- ISJSON
SELECT ISJSON(@json)

-- JSON_VALUE / JSON_QUERY
SELECT
   JSON_VALUE(@json, '$.DbName') as DatabaseName
,  JSON_VALUE(@json, '$.Properties.Collation') as Collation
,  JSON_VALUE(@json, '$.Properties.CompatibilityLevel') as CompatibilityLevel
,  JSON_VALUE(@json, '$.Properties.RecoveryModel') as RecoveryModel
-- JSON_QUERY
,  JSON_QUERY(@json, '$.Properties') as PropertiesJson

----------------------------------------------
-- TUNING - INDEX JSON Value
----------------------------------------------
use JSONDB
GO
-- DROP ... IF EXITS - NEW SQL 2016 syntax
DROP TABLE IF EXISTS dbo.DatabaseInformation
CREATE TABLE dbo.DatabaseInformation
(
   DbInfoId int identity(1,1) not null primary key
  ,DbInfoJson NVARCHAR(MAX) CONSTRAINT [Content should be formatted as JSON] CHECK (ISJSON(DbInfoJson)>0)
  ,DbName as CAST(JSON_VALUE(DbInfoJson, '$.DbName') AS NVARCHAR(128)) PERSISTED
);
-- Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'ix_DatabasesDbNameJson' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail.
-- Create index 
CREATE NONCLUSTERED INDEX ix_DatabasesDbNameJson on dbo.DatabaseInformation (DbName)
INCLUDE (DbInfoId, DbInfoJson);
GO

INSERT INTO dbo.DatabaseInformation
VALUES ('{
      "DbName": "master",
      "Properties": {
        "Collation": "Latin1_General_CI_AI",
        "CompatibilityLevel": 130,
        "RecoveryModel": "SIMPLE"
      }
    }'),
(
'{
      "DbName": "tempdb",
      "Properties": {
        "Collation": "Latin1_General_CI_AI",
        "CompatibilityLevel": 130,
        "RecoveryModel": "SIMPLE"
      }
    }'
),
 ('{
      "DbName": "model",
      "Properties": {
        "Collation": "Latin1_General_CI_AI",
        "CompatibilityLevel": 130,
        "RecoveryModel": "FULL"
      }
    }'
),
('{
      "DbName": "msdb",
      "Properties": {
        "Collation": "Latin1_General_CI_AI",
        "CompatibilityLevel": 130,
        "RecoveryModel": "SIMPLE"
      }
    }'
)
GO
-- INDEX SEEK SAMPLE
SELECT * from [dbo].[DatabaseInformation]
where DbName = 'model'
GO

-- OPENJSON / CROSS APPLY - Query JSON data.
SELECT
 j.DatabaseName
,j.Collation
,j.CompatibilityLevel
,j.RecoveryModel
,d.DbInfoJson
FROM [dbo].[DatabaseInformation] as d
CROSS APPLY OPENJSON (d.DbInfoJson)
WITH
(
   DatabaseName nvarchar(128) '$.DbName'
  ,Collation nvarchar(50) '$.Properties.Collation'
  ,CompatibilityLevel nvarchar(50) '$.Properties.CompatibilityLevel'
  ,RecoveryModel nvarchar(50) '$.Properties.RecoveryModel'
) as J
--where d.DbName = 'model'
GO


-- JSON_MODIFY Update the property in a JSON string
DECLARE @JSON NVARCHAR(MAX) 
SELECT TOP 1 @JSON = d.DbInfoJson
FROM [dbo].[DatabaseInformation] as d

select  @JSON

select JSON_MODIFY(@JSON, '$.Properties.RecoveryModel', 'N.V.T.')
GO

DBCC SQLPERF('sys.dm_os_wait_stats', clear)