Insert an object into a JSON array in SQL Server
You should wrap the third parameter of your JSON_MODIFY statement with JSON_QUERY():
UPDATE TheTable SET TheJSON = JSON_MODIFY(TheJSON, 'append $', JSON_QUERY(N'{"id": 3, "name": "Three"}')) WHERE Condition = 1;Here is a complete sample:
DECLARE @TheTable table(TheJSON nvarchar(max), Condition int )DECLARE @mystring nvarchar(100)='{"id": 3, "name": "Three"}'INSERT INTO @TheTable SELECT '[{"id": 1, "name": "One"}, {"id": 2, "name": "Two"}]', 1UPDATE @TheTable SET TheJSON = JSON_MODIFY(TheJSON, 'append $', JSON_QUERY(N'{"id": 3, "name": "Three"}')) WHERE Condition = 1;SELECT TheJSON FROM @TheTableThis is the final output:
[{"id": 1, "name": "One"}, {"id": 2, "name": "Two"},{"id": 3, "name": "Three"}]More info on JSON_QUERY here, and the explation of the issue is here.