title: TSQL how to merge two Json arrays
body: <p>How to merge new array elements into an existing Json property?
  Our two json objects look like this and we want to append the items of the 2nd json object to the 1st json object. (See desired result)</p>
  
  <p>Is there a way to achieve this with JSON_MODIFY?
  The microsoft documentation doesn't really show any example of multiple elements being merged into the already existing array. Only a single element. But we have a list of multiple elements that need to be merged.</p>
  
  <p><strong>Edit:</strong>
  JSON_MODIFY(@json1, 'append $.Items', JSON_QUERY(@json2)) seems to create new array brackets instead of merging the items into the array.</p>
  
  <p>Json Object 1:</p>
  
  <pre><code>DECLARE @json1 NVARCHAR(MAX) = '{
      "id": 1,
      "Items": [
          {
              "id": 1,
              "name" : "Item #1"
          },
          {
              "id": 2,
              "name" : "Item #2"
          }
      ]
  }'
  </code></pre>
  
  <p>Json Object 2:</p>
  
  <pre><code>DECLARE @json2 NVARCHAR(MAX)='{
      "Items": [
          {
              "id": 3,
              "name": "Item #3"
          },
          {
              "id": 4,
              "name": "Item #4"
          }
      ]
  }'
  </code></pre>
  
  <p>Desired Result:</p>
  
  <pre><code>{
      "id": 1,
      "Items": [
          {
              "id": 1,
              "name": "Item #1"
          },
          {
              "id": 2,
              "name": "Item #2"
          },
          {
              "id": 3,
              "name": "Item #3"
          },
          {
              "id": 4,
              "name": "Item #4"
          }
      ]
  }
  </code></pre>