Insert an object into a JSON array in SQL Server

Every example that I've seen for JSON_MODIFY shows inserting a simple value such as a string into an array.

Suppose I have the following JSON stored in my SQL Server column:

[{"id": 1, "name": "One"}, {"id": 2, "name": "Two"}]

How do I append {"id": 3, "name": "Three"} to it?

When I try using JSON_MODIFY as shown below, a string is inserted:

UPDATE TheTable SET TheJSON = JSON_MODIFY(TheJSON, 'append $', N'{"id": 3, "name": "Three"}') WHERE Condition = 1;

Here is the resulting value for TheJSON column:

[{"id": 1, "name": "One"}, {"id": 2, "name": "Two"}, "{\"id\":3, \"name\": \"Three\"}"]

Other Attempts:

I noticed that I can create the JSON string that I want like this:

SELECT json.*
FROM TheTable t
CROSS APPLY OPENJSON(t.TheJSON) WITH (
    id int N'$.id',
    name nvarchar(100) N'$.name'
)
UNION ALL
SELECT 3 as id, N'Three' as name
FOR JSON AUTO;

However, when I go to try and use it in an update statement, it doesn't work:

UPDATE TheTable
SET TheJSON = (
    SELECT json.* FROM TheTable t
    CROSS APPLY OPENJSON(t.TheJSON) WITH (
        id int N'$.id',
        name nvarchar(100) N'$.name'
    ) as json
    UNION ALL -- NO ERROR (and no update) when I remove UNION ALL+SELECT
    SELECT 3 as id, N'Three' as name
    FOR JSON AUTO
);

I get the following error:

Msg 1086, Level 15, State 1, Line 1: The FOR XML and FOR JSON clauses are invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table or common table expression or view and apply FOR XML or FOR JSON on top of it.

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"}]', 1

UPDATE @TheTable 
SET TheJSON = JSON_MODIFY(TheJSON, 'append $', JSON_QUERY(N'{"id": 3, "name": "Three"}')) 
WHERE Condition = 1;

SELECT TheJSON FROM @TheTable

This 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.