title: MySQL JSON_SET() to work with empty arrays
body: <p>I have a table like this:</p>
  
  <pre><code>CREATE TABLE `test` (
    `id` int(11) NOT NULL,
    `settings` json NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
  
  INSERT INTO `test` (`id`, `settings`) VALUES 
      ('1', '{\"foo\": {\"bar\": 1}}'),
      ('2', '{\"foobar\": 2}'),
      ('3', '[]');
  </code></pre>
  
  <p>I want to add a new setting to a row, so I tried <a href="https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-set" rel="nofollow noreferrer">JSON_SET</a>:</p>
  
  <pre><code>SELECT *, JSON_SET(settings, '$.newFoo', 10) FROM test;
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/L5duF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/L5duF.png" alt="enter image description here"></a></p>
  
  <p>As you can see, it doesn't work on the item 3. The expected result is of course the same as on line 4; <code>[]</code> is the result of json-encoding an empty array in php, if that item doesn't have any setting.</p>
  
  <p>Can make a single query that works for all the cases?</p>
  
  <hr>
  
  <p>P.S.: What I need to do is an UPDATE, i.e. <code>UPDATE test SET settings=JSON_SET(...) WHERE id=?;</code></p>