Extracting data from an array of JSON objects for specific object values

Question

In my table, there is a column of JSON type which contains an array of objects describing time offsets:

[
  {
    "type": "start"
    "time": 1.234
  },
  {
    "type": "end"
    "time": 50.403
  }
]

I know that I can extract these with JSON_EACH() and JSON_EXTRACT():

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;

This returns a table like:

start    12.345
end      67.891
start    24.56
end      78.901

What I really need though is to:

1. Find the timings of specific types. (Find the first object in the array that matches a condition.)
2. Take this data and select it as a column with the rest of the table.

In other words, I'm looking for a table that looks like this:

id        start        end
-----------------------------
0         12.345       67.891
1         24.56        78.901

I'm hoping for some sort of query like this:

SELECT
  id,
  JSON_EXTRACT(timings, '$.[type="start"].time'),
  JSON_EXTRACT(timings, '$.[type="end"].time')
FROM Items;

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?

Answer 1

One possibility:

WITH cte(id, json) AS
  (SELECT Items.id
        , json_group_object(json_extract(j.value, '$.type'), json_extract(j.value, '$.time'))
   FROM Items
   JOIN json_each(timings) AS j ON json_extract(j.value, '$.type') IN ('start', 'end')
   GROUP BY Items.id)
SELECT id
     , json_extract(json, '$.start') AS start
     , json_extract(json, '$.end') AS "end"
FROM cte
ORDER BY id;

which gives

id          start       end
----------  ----------  ----------
1           12.345      67.891
2           24.56       78.901

Another one, that uses the window functions added in sqlite 3.25 and avoids creating intermediate JSON objects:

SELECT DISTINCT Items.id
              , max(json_extract(j.value, '$.time'))
                 FILTER (WHERE json_extract(j.value, '$.type') = 'start') OVER ids AS start
              , max(json_extract(j.value, '$.time'))
                 FILTER (WHERE json_extract(j.value, '$.type') = 'end') OVER ids AS "end"
FROM Items
JOIN json_each(timings) AS j ON json_extract(j.value, '$.type') IN ('start', 'end')
WINDOW ids AS (PARTITION BY Items.id)
ORDER BY Items.id;

The key is using the ON clause of the JOIN to limit results to just the two objects in each array that you care about, and then merging those up to two rows for each Items.id into one with a couple of different approaches.