web_application

Column	Type
Title
SQL DDL

CREATE VIEW web_application AS
SELECT DISTINCT
    json_extract(border.value, '$."a:Value".Properties."a:anyType"[1]."b:Value"."#text"') AS Title
FROM 
   uniform_resource_transform,
    json_each(
        CASE 
            WHEN json_type(json_extract(content, '$.ThreatModel.DrawingSurfaceList.DrawingSurfaceModel')) = 'array' 
            THEN json_extract(content, '$.ThreatModel.DrawingSurfaceList.DrawingSurfaceModel')
            ELSE json_array(json_extract(content, '$.ThreatModel.DrawingSurfaceList.DrawingSurfaceModel'))
        END
    ) AS drawing_surface,
    json_each(
        json_extract(drawing_surface.value, '$.Borders."a:KeyValueOfguidanyType"')
    ) AS border
WHERE  json_extract(border.value, '$."a:Value"."@i:type"') = 'StencilEllipse'
    AND json_extract(border.value, '$."a:Value".Properties."a:anyType"[0]."b:DisplayName"') = 'Web Application'