In Multi-Objects JSON array, I would like to modify all the values of objects and I have difficulty in accessing the values i.e.,
EXAMPLE--
DECLARE @json nvarchar(MAX)
SET @json = N'[{"name": "John","sex": "F"}, {"name": "Jane","sex": "F"}]'
I would like to modify sex value of all objects to "M".
JSON_MODIFY(@json, '$.Sex', 'M')
is not working. Are there any methods to solve my issue?
This should help
DECLARE @jsonstr NVARCHAR(MAX) = '[{"name": "John","sex": "F"}, {"name": "Jane","sex": "F"}]}'
;WITH CTE AS
(
SELECT * FROM OPENJSON(@jsonstr)
WITH ([name] VARCHAR(100) '$.name' , [sex] VARCHAR(100) '$.sex' )
)
,CTE1 AS
(
SELECT [name], case when [sex] = 'F' THEN 'M' ELSE [sex] END [sex]
FROM CTE
)
SELECT *
FROM CTE1
FOR JSON AUTO
GO
OUTPUT
[{"name":"John","sex":"M"},{"name":"Jane","sex":"M"}]
