Using JSON in SQL Server

If you want to extract a single value out of a JSON string, you can use the JSON_VALUE() function. Note that parameters passed to it are case sensitive. By default, any errors are ignored. You pass this function a JSON variable to interrogate, plus a path to the value of interest. The output is a single value from an element. Note that if you are using arrays, they use zero-based indexes.

If you want to extract an object/array from a JSON type, you use the JSON_QUERY() function. The output will be in XML format. You pass this function a JSON variable, plus a location in single quotes. The dollar sign represents the root.

You can also make changes to a JSON object using the JSON_MODIFY() function. Or if you want to convert JSON to a SQL table, use the OPENJSON() function. Finally to go the other way around, to produce JSON from a database table, you use FOR JSON PATH, which if kind of like FOR XML PATH.