title: Insert an object into a JSON array in SQL Server
body: <p>Every example that I've seen for <code>JSON_MODIFY</code> shows inserting a simple value such as a <code>string</code> into an array.</p>
  
  <p>Suppose I have the following JSON stored in my SQL Server column:</p>
  
  <p><code>[{"id": 1, "name": "One"}, {"id": 2, "name": "Two"}]</code></p>
  
  <p>How do I append <code>{"id": 3, "name": "Three"}</code> to it?</p>
  
  <p>When I try using <code>JSON_MODIFY</code> as shown below, a <code>string</code> is inserted:</p>
  
  <p><code>UPDATE TheTable SET TheJSON = JSON_MODIFY(TheJSON, 'append $', N'{"id": 3, "name": "Three"}') WHERE Condition = 1;</code></p>
  
  <p>Here is the resulting value for <code>TheJSON</code> column:</p>
  
  <p><code>[{"id": 1, "name": "One"}, {"id": 2, "name": "Two"}, "{\"id\":3, \"name\": \"Three\"}"]</code></p>
  
  <h3>Other Attempts:</h3>
  
  <p>I noticed that I can create the JSON string that I want like this:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>However, when I go to try and use it in an update statement, it doesn't work:</p>
  
  <pre><code>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
  );
  </code></pre>
  
  <p>I get the following error:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>