title: mysql 5.7 appending key/value to nested json object
body: <p>I'm trying the new API for mysql 5.7 which deals with JSON columns.  My <code>test</code> column looks like this:</p>
  
  <pre><code>{"foo":{"efg":1}, "bar":{"abc":0}}
  </code></pre>
  
  <p>What I would like to do is append to one of the keys, for example <code>foo</code> so it will result in <code>"foo":{"efg":1, "klm":2}</code>.  What I've tried so far following <a href="https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html" rel="nofollow">their documentation</a>:</p>
  
  <pre><code>mysql&gt; select json_insert(test, '$.foo', 10, '$.foo.klm', 2) from table1
         where name='Joe';
  </code></pre>
  
  <p>What that does is replace <code>"efg":1</code> and the result is <code>"foo":{"klm":2}</code>.</p>
  
  <pre><code>mysql&gt; select json_array_append(test, '$.foo', '{"klm":2}') from table1 where
         name="Joe';
  </code></pre>
  
  <p>The above line obviously converts <code>foo</code> into an array <code>"foo":[{"efg":1}, {"klm":2}]</code>, which is not what I want.</p>
  
  <p>I've tried combining queries together: </p>
  
  <pre><code>mysql&gt; select json_insert(test, '$.foo', 10, '$.foo', 
         select json_merge(select json_extract(test, '$.foo') from table1 
         where name="Joe"), '{"klm":2}') from table1 where name="Joe";
  </code></pre>
  
  <p>That just gives me a syntax error <code>near select json_extract(test, '$.foo')</code>.</p>
  
  <p>Any advice would be much appreciated.</p>