Complex SQL query by pattern with timestamps

I have the following info in my SQLite database:

ID | timestamp  | val
1  | 1577644027 | 0
2  | 1577644028 | 0
3  | 1577644029 | 1
4  | 1577644030 | 1
5  | 1577644031 | 2
6  | 1577644032 | 2
7  | 1577644033 | 3
8  | 1577644034 | 2
9  | 1577644035 | 1
10 | 1577644036 | 0
11 | 1577644037 | 1
12 | 1577644038 | 1
13 | 1577644039 | 1
14 | 1577644040 | 0

I want to perform a query that returns the elements that compose an episode. An episode is a set of ordered registers that comply the following requirements:

1. The first element is greater than zero.
2. The previous element of the first one is zero.
3. The last element is greater than zero.
4. The next element of the last one is zero.

The expected result of the query on this example would be something like this:

[

[{"id":3, tmstamp:1577644029, value:1}
{"id":4, tmstamp:1577644030, value:1}
{"id":5, tmstamp:1577644031, value:2}
{"id":6, tmstamp:1577644032, value:2}
{"id":7, tmstamp:1577644033, value:3}
{"id":8, tmstamp:1577644034, value:2}
{"id":9, tmstamp:1577644035, value:1}],

[{"id":11, tmstamp:1577644037, value:1}
{"id":12, tmstamp:1577644038, value:1}
{"id":13, tmstamp:1577644039, value:1}]

]

Currently, I am avoiding this query and I am using an auxiliary table to store the initial and end timestamp of episodes, but this is only because I do not know how to perform this query.

Threfore, my question is quite straightforward: does anyone know how can I perform this query in order to obtain something similar to the stated ouput?

Answer

If you want the result as JSON objects then you must use the JSON1 Extension functions of SQLite:

with cte as (
  select *, sum(val = 0) over (order by timestamp) grp
  from tablename
) 
select 
  json_group_array(
    json_object('id', id, 'timestamp', timestamp, 'val', val)
  ) result
from cte
where val > 0
group by grp

See the demo.
Results:

| result                                                                                                                                                                                                                                                                                    |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| [{"id":3,"timestamp":1577644029,"val":1},{"id":4,"timestamp":1577644030,"val":1},{"id":5,"timestamp":1577644031,"val":2},{"id":6,"timestamp":1577644032,"val":2},{"id":7,"timestamp":1577644033,"val":3},{"id":8,"timestamp":1577644034,"val":2},{"id":9,"timestamp":1577644035,"val":1}] |
| [{"id":11,"timestamp":1577644037,"val":1},{"id":12,"timestamp":1577644038,"val":1},{"id":13,"timestamp":1577644039,"val":1}]                                                                                                                                                              |