title: JSON - How to append an array to an array in sql
body: <p>I have a json field in a table that contains an array like this:-</p>
  
  <pre><code>[
    {
      "ID": 11111,
      "Name": "apple",
    },
    {
      "ID": 22222,
      "Name": "orange",
    },
    {
      "ID": 333333,
      "Name": "banana",
    } 
  ] 
  </code></pre>
  
  <p>I would like to append/concatenate the following json array to this one:-</p>
  
  <pre><code>[
    {
      "ID": 44444,
      "Name": "grape",
    },
    {
      "ID": 55555,
      "Name": "kiwi",
    },
    {
      "ID": 66666,
      "Name": "fig",
    } 
  ] 
  </code></pre>
  
  <p>So that I end up with this in the table field:-</p>
  
  <pre><code>[
    {
      "ID": 11111,
      "Name": "apple",
    },
    {
      "ID": 22222,
      "Name": "orange",
    },
    {
      "ID": 333333,
      "Name": "banana",
    },
    {
      "ID": 44444,
      "Name": "grape",
    },
    {
      "ID": 55555,
      "Name": "kiwi",
    },
    {
      "ID": 66666,
      "Name": "fig",
    } 
  ] 
  </code></pre>
  
  <p>i.e. I've added the three new elements to the three existing elements so that i now have a single array with six elements in my table field.</p>
  
  <p>I have been trying to make this work with JSON_MODIFY and have been successful in adding a single element to the array with something like this:-</p>
  
  <pre><code>select JSON_MODIFY(json_field,'append $', JSON_QUERY('{ "ID": 44444, "Name": "grape" }'))
  </code></pre>
  
  <p>But I cannot make it append more than one element in a single operation and make it look as desired, I've been trying variations of this:-</p>
  
  <pre><code>select JSON_MODIFY(json_field,'append $', JSON_QUERY('[{ "ID": 44444, "Name": "grape" }, { "ID": 55555, "Name": "kiwi" }, { "ID": 66666, "Name": "fig" }]'))
  </code></pre>
  
  <p>In this particular case, it appended it with the square brackets so the three new elements ended up being a sub-array!</p>
  
  <p>Is it possible to get append multiple elements of one array to another like this? (Am I being really thick and missing something obvious?!?)</p>