Check if JSON contains a property

Question

I am reading microsoft docs ms sql JSON options And I would like to check if my column Settings which is nvarchar, I put there some JSON, and if that JSON contains property name.

Is it doable?

something like:

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
IF (ISJSON(@info) > 0)  
BEGIN  
     -- if (@info contains `$.name`) 
     --begin
        -- SET @info=JSON_MODIFY(@info,'$.name','Mike')
     --end
     -- else 
     --begin
       --  SET @info=JSON_MODIFY(@info,'$.name','Mike')
     --end
END

I need to check if that JSON property exists, if exists then just update value, if not then insert that property with predefined value.

Cedillo

SSMS is just a client tool. It doesn't affect how the query runs and the latest version is 18.2. I suspect you meant SQL Server 2017? – Santanasantayana

@PanagiotisKanavos Yes, so? I didnt mention ssms? – Cedillo

As for checking and modifying, you'd do it the same way you'd update any other data - not with an IF but a condition in WHERE. If you want to check for the existence of a specific property you don't need to use ISJSON first. Use JSON_VALUE and check its return value – Santanasantayana

You used the SSMS, SSMS-2017 tags which I removed. – Santanasantayana

Answer 1

How to insert or update key: value pair:

If you want to " ... check if that JSON property exists, if exists then just update value, if not then insert that property with predefined value ...", the remarks section from the JSON_MODIFY documentation gives you one possible solution:

The JSON_MODIFY function lets you either update the value of an existing property, insert a new key:value pair, or delete a key based on a combination of modes and provided values.

If you execute JSON_MODIFY with not NULL value, in lax mode, the function will try to:

• insert a new key:value pair if the specified path doesn't exist.
• update the existing key: value pair if the specified path exists.

Table:

CREATE TABLE #Data (
   Settings nvarchar(100)
)
INSERT INTO #Data
   (Settings)
VALUES
   (N'{"name":"John","skills":["C#","SQL"]}')

Statement:

-- Update existing key
UPDATE #Data
SET Settings = JSON_MODIFY(Settings, 'lax $.name', 'Mike')
WHERE ISJSON(Settings) = 1

-- Insert new key
UPDATE #Data
SET Settings = JSON_MODIFY(Settings, 'lax $.day', 'Sunday')
WHERE ISJSON(Settings) = 1

Output:

SELECT *
FROM #Data

Settings
{"name":"Mike","skills":["C#","SQL"],"day":"Sunday"}

How to check for specific key or value:

If you want to check if specific key exists, use OPENJSON():

SELECT *
FROM #Data d
CROSS APPLY OPENJSON(d.Settings) j
WHERE j.[key] = 'name'

If you want to check if specific value exists, use JSON_VALUE():

SELECT *
FROM #Data
WHERE JSON_VALUE(Settings, '$.name') = 'Mike'

Example with the statement from the question:

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
IF (ISJSON(@info) > 0)  BEGIN  
   IF EXISTS(SELECT 1 FROM OPENJSON(@info) WHERE [key] = 'name') PRINT 'Yes'
   ELSE PRINT 'No'
END

New JSON features in SQL Server 2022:

Starting from SQL Server 2022, you may use JSON_PATH_EXISTS() function to check whether a specified JSON path exists:

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
IF ISJSON(@info) = 1 BEGIN  
   IF JSON_PATH_EXISTS(@info, '$.name') = 1 PRINT 'Yes'
   ELSE PRINT 'No'
END

Arbiter

you checking ISJSON formatted as JSON, but how can I check if already exists property name in JSON? I dont want to insert another property name if one elaready exists. – Cedillo

@Stefan0309 JSON_MODIFY will not append another name property in the JSON text. It will overwrite the value, if the key exists, or append a new key with the new value. – Arbiter

That helped me a lot! To run version with OPENJSON I needed to increase compatibility_level of DB by running "ALTER DATABASE "db-name" SET COMPATIBILITY_LEVEL = 130;" in mssms – Battik