Open-data restaurant inspection documents are stored as JSON and analysts filter heavily on a nested attribute path. Which indexing approach should the team design?
Select an answer to reveal the explanation.
Short Explanation
Persisting the hot JSON path and indexing it is like tabbing the “critical violation” page in every inspection packet so analysts are not rereading the whole binder. Open-data filters stay fast without abandoning the flexible civic document.
Full Explanation
Restaurant inspection open-data documents stored as JSON are flexible, but analysts repeatedly filter on nested attribute paths. Persisting those hot paths as computed persisted columns and applying JSON indexing guidance lets Azure SQL build selective access paths instead of scanning entire nvarchar(max) documents for every civic dashboard filter. That design keeps the JSON payload while making the frequent predicate sargable and maintainable for municipal open-data workloads. Leaving JSON unindexed forces end-to-end document scans as volume grows. Converting the entire document into a single timestamp discards the inspection payload analysts need. Filestream image storage outside ordinary SQL queryability removes T-SQL filtering of attributes altogether. Caveat: persist and index only stable, selective paths; indexing every nested key creates write overhead and limited benefit for rarely filtered attributes. Revisit the persisted path if the open-data schema version changes. Operational check: add the persisted computed column for the nested path, create the appropriate index, and verify a representative inspection filter uses the index rather than a full document scan in the actual execution plan.