Can I modify all values in Multi-objects JSON array in SQL server?

Question

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?

Answer 1

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"}]