title: MySQL JSON extract a single value by id
body: <p>I have a database with a JSON array in column <code>extra_fields</code>. I need to extract one value by id. </p>
  
  <p>The JSONs looks something like this, though the number of objects is random for each row:</p>
  
  <pre><code>[
  {"id":"1","value":"48768"},
  {"id":"2","value" ["String","http:someurl"]},
  {"id":"5","value":"somevalue"},
  {"id":"6","value":""},
  {"id":"8","value":"Op-Ed"},
  {"id":"9","value":"8111,13498,15408"},
  {"id":"10","value":"30"},
  {"id":"11","value":"This is the target string"}
  ]
  </code></pre>
  
  <p>I can extract an array of ids with:</p>
  
  <pre><code>SELECT extra_fields-&gt;&gt;"$[*].id"  FROM esqt7_k2_items;
  </code></pre>
  
  <p>I can extract an array of values with: </p>
  
  <pre><code>SELECT extra_fields-&gt;&gt;"$[*].value"  FROM esqt7_k2_items;
  </code></pre>
  
  <p>I can extract a single value at the nth zero-ordered object position with: </p>
  
  <pre><code>SELECT extra_fields-&gt;&gt;"$[2].value"  FROM esqt7_k2_items;
  </code></pre>
  
  <p>But the problem is that there's a variable number of objects and I specifically need the value of object id = 11. JSON_EXTRACT doesn't seem to support filtering, as every variation of a JSONPATH seems to fail. <code>$..[?(@.id=11)].value</code> works as a JSONPATH, but this (and many variants) fails:</p>
  
  <p><code>SELECT extra_fields-&gt;"$.[?(@.id=11)].value" FROM esqt7_k2_items; #FAILS</code></p>
  
  <p>This is a migration project and the JSON stuff is just how Joomla chose to implement their extra fields. Kind of a pain if you ask me. </p>