title: mysql JSON_SET or JSON_INSERT function to insert object into nested object if it exists
body: <p>I have a json type column <code>config</code> I want to add a key value pair into, ultimately looking like:</p>
  
  <pre><code>{
    "features": {
      "divisions": true,
      "utilities": {
          "water": true,
          "gas": true,
          "electric": true
      }
    }
  }
  </code></pre>
  
  <p>The issue I'm running into is when I want to insert the utilities object inside of features, I either overwrite the <code>divisions</code> key value or I'm returning <code>NULL</code> and the <code>utilities</code> object isn't being inserted.</p>
  
  <p>Furthermore, the <code>config</code> column may be <code>NULL</code> or initially just be an empty <code>{}</code>.</p>
  
  <p>This query will check for NULL or empty {} and also whether the <code>features</code> key exists but results in overwriting <code>features</code> if it already exists:</p>
  
  <pre><code>UPDATE entities SET config = JSON_SET(COALESCE(config, '{}'),
    COALESCE("$.features", "features"), JSON_OBJECT("utilities", 
    JSON_OBJECT("water", TRUE, "gas", TRUE, "electric", TRUE))) 
    WHERE id = 123725082;
  </code></pre>
  
  <p>This works fine unless the column already contains something like:</p>
  
  <pre><code>{
    "features": {
      "divisions": true,
    }
  }
  </code></pre>
  
  <p>in which it overwrites <code>divisions</code> with the <code>utilities</code> object.</p>
  
  <p>So I'm trying a JSON_INSERT query; from what I've gathered from mysql json functions documentation should work but it's returning null and I can't understand why:</p>
  
  <pre><code>UPDATE entities SET config = JSON_INSERT(COALESCE(config, '{}'),
    COALESCE("$.features", "features"), JSON_OBJECT("utilities", 
    JSON_OBJECT("water", TRUE, "gas", TRUE, "electric", TRUE))) 
    WHERE id = 123725082;
  </code></pre>