Bert Wagner gives us a way that you can quickly search through complicated JSON:
Computed column indexes make querying JSON data fast and efficient, especially when the schema of the JSON data is the same throughout a table.
It’s also possible to break out a well-known complex JSON structure into multiple SQL Server tables.
However, what happens if you have different JSON structures being stored in each row of your database and you want to write efficient search queries against all of the rows of your complex JSON strings?
Bert’s solution is an example of a phenomenon I’ve noticed in relational databases: sometimes, the best solution is not the most straightforward. The most straightforward solution is to take the JSON as-is, but that hits a wall as Bert shows. Reshaping the data leads to much better performance…as long as you’re able to afford the time needed to reshape and don’t have JSON changing that frequently.