title: How do I select and update a JSON array element in Postgresql?
body: <p>I'm using version 9.6. I have documents that look like this:</p>
  
  <pre><code>{
      "name" : "John Doe", 
      "phones" : [
          {
              "type" : "mobile", 
              "number" : "555-555-0000",
              "deleted": false
          }, 
          {
              "type" : "home", 
              "number" : "555-555-0001",
              "needsUpdated" : true
          }, 
          {
              "type" : "work", 
              "number" : "555-555-0002"
          }
      ]
  }
  </code></pre>
  
  <p>I created them like this: </p>
  
  <pre><code>create table t_json (c_json json not null);
  
  insert into t_json (c_json) values ('{"name":"John Doe","phones": [{"type":"mobile","number":"555-555-0000"},{"type":"home","number":"555-555-0001"},{"type": "work","number": "555-555-0002"}]}');
  
  insert into t_json (c_json) values ('{"name":"Jane Dane","phones": [{"type":"mobile","number":"555-555-0030"},{"type":"home","number":"555-555-0020"},{"type": "work","number": "555-555-0010"}]}');
  </code></pre>
  
  <p>Now I'm trying to figure out how to <code>A</code>, select the row with the name <strong>John Doe</strong>, and update his mobile number to "555-555-0003".</p>
  
  <p>From here <a href="https://www.postgresql.org/docs/current/static/functions-json.html" rel="noreferrer">Postgresql 9.6 documentation</a> I figured out that I could query for the proper document like this:</p>
  
  <pre><code>select c_json from t_json where c_json-&gt;&gt;'name' = 'John Doe';
  </code></pre>
  
  <p>But I'm failing to see how to select the proper sub-document in the phones array by type, and update the number value.  Can anyone help me out?</p>
  
  <p>EDIT</p>
  
  <p>I need to assume the sub-documents have extra values and are not consistent.  So I added some above.  I'm pretty sure this update isn't possible without data loss at this point.</p>