A 311 intake system stores free-form request payloads and must filter efficiently by category keys inside those documents. What should the team implement?
Select an answer to reveal the explanation.
Short Explanation
311 payloads are messy shopping lists stuffed in one bag—a JSON column keeps the bag flexible, and a JSON index is the sticker that lets you find “pothole” without unpacking every bag. That is how civic intake stays queryable without pretending every field was born as a tidy table column.
Full Explanation
311 intake payloads are semi-structured: categories, locations, and free-text details vary by request type. A JSON column stores those documents without forcing a brittle wide table for every possible field, while JSON indexing (and related computed persisted paths where appropriate) lets Azure SQL filter efficiently on keys such as category. That combination preserves flexibility for civic intake and keeps operational dashboards selective. Storing payloads only as image blobs and full-text searching binary streams fights structured key filters and complicates ordinary T-SQL predicates. Exploding every possible field into hundreds of sparse bit columns abandons the semi-structured nature of 311 payloads and creates schema churn whenever a new category attribute appears. Undersized varchar storage truncates keys and values, silently damaging categories that must remain searchable. Caveat: JSON indexes help known, selective paths; deeply nested or highly variable documents still need disciplined path design and may benefit from persisted computed columns for the hottest filters. Operational check: load representative 311 JSON documents, create the supported JSON index or persisted path used for category, and confirm a category equality filter seeks or otherwise avoids a full nvarchar(max) scan in the plan.