threat_model

Column	Type
id
title
category
short_description
description
interaction
priority
state
justification
changed_by
last_modified
SQL DDL

CREATE VIEW threat_model AS
WITH json_data AS (
    SELECT
        json_extract(threats.value, '$."a:Value"."b:Id"') AS "id",
        json_extract(threats.value, '$."a:Value"."b:ChangedBy"') AS "changed_by",
        json_extract(threats.value, '$."a:Value"."b:ModifiedAt"') AS "last_modified",
        json_extract(threats.value, '$."a:Value"."b:State"') AS "state",
        json_each.value AS kv_value
    FROM 
        uniform_resource_transform,
        json_each(json_extract(content, '$.ThreatModel.ThreatInstances."a:KeyValueOfstringThreatpc_P0_PhOB"')) AS threats,
        json_each(json_extract(threats.value, '$."a:Value"."b:Properties"."a:KeyValueOfstringstring"'))
)
SELECT
    id,
    MAX(CASE WHEN json_extract(kv_value, '$."a:Key"') = 'Title' THEN json_extract(kv_value, '$."a:Value"') END) AS "title",
    MAX(CASE WHEN json_extract(kv_value, '$."a:Key"') = 'UserThreatCategory' THEN json_extract(kv_value, '$."a:Value"') END) AS "category",
    MAX(CASE WHEN json_extract(kv_value, '$."a:Key"') = 'UserThreatShortDescription' THEN json_extract(kv_value, '$."a:Value"') END) AS "short_description",
    MAX(CASE WHEN json_extract(kv_value, '$."a:Key"') = 'UserThreatDescription' THEN json_extract(kv_value, '$."a:Value"') END) AS "description",
    MAX(CASE WHEN json_extract(kv_value, '$."a:Key"') = 'InteractionString' THEN json_extract(kv_value, '$."a:Value"') END) AS "interaction",
    MAX(CASE WHEN json_extract(kv_value, '$."a:Key"') = 'Priority' THEN json_extract(kv_value, '$."a:Value"') END) AS "priority",
    state,
    MAX(CASE WHEN json_extract(kv_value, '$."a:Key"') = 'StateInformation' THEN json_extract(kv_value, '$."a:Value"') END) AS "justification",
    "changed_by",
    "last_modified"
FROM
    json_data
GROUP BY
    id, "changed_by", "last_modified", state