title: Extracting data from an array of JSON objects for specific object values
body: <p>In my table, there is a column of <code>JSON</code> type which contains an array of objects describing time offsets:</p>
  
  <pre><code>[
    {
      "type": "start"
      "time": 1.234
    },
    {
      "type": "end"
      "time": 50.403
    }
  ]
  </code></pre>
  
  <p>I know that I can extract these with <code>JSON_EACH()</code> and <code>JSON_EXTRACT()</code>:</p>
  
  <pre><code>CREATE TEMPORARY TABLE Items(
    id INTEGER PRIMARY KEY,
    timings JSON
  );
  
  INSERT INTO Items(timings) VALUES
    ('[{"type": "start", "time": 12.345}, {"type": "end", "time": 67.891}]'),
    ('[{"type": "start", "time": 24.56}, {"type": "end", "time": 78.901}]');
  
  SELECT
    JSON_EXTRACT(Timings.value, '$.type'),
    JSON_EXTRACT(Timings.value, '$.time')
  FROM
    Items,
    JSON_EACH(timings) AS  Timings;
  </code></pre>
  
  <p>This returns a table like:</p>
  
  <pre><code>start    12.345
  end      67.891
  start    24.56
  end      78.901
  </code></pre>
  
  <p>What I really need though is to:</p>
  
  <ol>
  <li>Find the timings of specific types.  (Find the first object in the array that matches a condition.)</li>
  <li>Take this data and select it as a column with the rest of the table.</li>
  </ol>
  
  <p>In other words, I'm looking for a table that looks like this:</p>
  
  <pre><code>id        start        end
  -----------------------------
  0         12.345       67.891
  1         24.56        78.901
  </code></pre>
  
  <p>I'm hoping for some sort of query like this:</p>
  
  <pre><code>SELECT
    id,
    JSON_EXTRACT(timings, '$.[type="start"].time'),
    JSON_EXTRACT(timings, '$.[type="end"].time')
  FROM Items;
  </code></pre>
  
  <p>Is there some way to use path in the JSON functions to select what I need?  Or, some other way to pivot what I have in the first example to apply to the table?</p>