SQL Server ❤️ JSON

In the modern world, you often have data that doesn't fit into neat columns (e.g., User Preferences or Log Metadata). Instead of switching to a NoSQL database, you can use SQL Server's powerful JSON support to handle both relational and semi-structured data in one place.

1. JSON_VALUE vs JSON_QUERY

• JSON_VALUE: Extracts a scalar (single string/number) value from a JSON string.
• JSON_QUERY: Extracts an entire object or array.

SELECT JSON_VALUE(MetaData, '$.Browser') AS Browser
FROM AppLogs WHERE JSON_VALUE(MetaData, '$.Status') = 'Error'

2. FOR JSON PATH

You can also turn your SQL results into JSON instantly! This is perfect for building REST APIs. Instead of mapping SQL rows to C# objects and then to JSON, you can have SQL Server output the JSON directly, saving massive amounts of Web Server CPU.

4. Interview Mastery

Q: "Is it efficient to index a JSON column in SQL Server?"

Architect Answer: "You cannot directly index the JSON blob. However, you can create a **Computed Column** that extracts a specific value from the JSON and then put an index on that computed column. This gives you 'NoSQL Flexibility' with 'Relational Speed'. It is a powerful hybrid strategy for modern cloud applications."