boundaries

CREATE VIEW boundaries AS
SELECT DISTINCT
    json_extract(border.value, '$."a:Value".Properties."a:anyType"[1]."b:Value"."#text"') AS boundary
    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"') = 'BorderBoundary'
    AND (
        json_extract(border.value, '$."a:Value".Properties."a:anyType"[0]."b:DisplayName"') IN (
            'Other Browsers Boundaries', 
            'CorpNet Trust Boundary', 
            'Generic Trust Border Boundary'
        )
    )
    AND json_extract(border.value, '$."a:Value".Properties."a:anyType"[1]."b:Value"."#text"') NOT LIKE '%.%.%.%'
    AND json_extract(border.value, '$."a:Value".Properties."a:anyType"[1]."b:Value"."#text"') LIKE '%Boundar%'