CREATE VIEW test_execution_log AS
SELECT
json_extract(content, '$.test_case_fii') AS test_case_id,
json_extract(content, '$.title') AS title,
json_extract(content, '$.status') AS status,
json_extract(value, '$.step') AS step_number,
json_extract(value, '$.stepname') AS step_name,
json_extract(value, '$.status') AS step_status,
strftime('%d-%m-%Y %H:%M:%S', json_extract(value, '$.start_time')) AS step_start_time,
strftime('%d-%m-%Y %H:%M:%S', json_extract(value, '$.end_time')) AS step_end_time
FROM
uniform_resource,
json_each(json_extract(content, '$.steps')) -- Expands the steps array into rows
WHERE
uri LIKE '%.result.json'