MSSQL - How to select JSON values from a column and parse them to decimals or integers using JSON_VALUE

In this post I show how to select and cast values from a column containing JSON so that you can work with the fields as you need to. They can be selected as individual select statements or used in where statements if needed.

An example

In this Example we will use a simple table with a primary key and a JsonContent column which is a simple NVARCHAR(MAX) column:

CREATE TABLE People(
	Id UNIQUEIDENTIFIER PRIMARY KEY,
	JsonContent NVARCHAR(MAX)
)

We insert an id and a simple JSON string so that we have something to select from. The JSON string that we insert contains a name (NVARCHAR), Age (Int) and Height (Decimal):

INSERT INTO People (Id, JsonContent) VALUES (NEWID(), N'{
   "Name" : "Peter",
   "Age" : 30,
   "Height" : 186.5
}');

We then use JSON_VALUE to get these individual fields from the JSON. Below is an example where each of the fields are selected and provided a name:

SELECT Id,
       JSON_VALUE(JsonContent, '$.Name') AS ParsedName,
       CAST(JSON_VALUE(JsonContent, '$.Age') AS INT) AS ParsedAge,
	   CAST(JSON_VALUE(JsonContent, '$.Height') AS DECIMAL(15,2)) AS ParsedDecimal
FROM People

This gives the following output:

Our string of JSON has now been selected and returned as different columns. The fields can also be used in WHERE clauses using their given names. For example:

SELECT Id,
       JSON_VALUE(JsonContent, '$.Name') AS ParsedName
FROM People
       WHERE CAST(JSON_VALUE(JsonContent, '$.Age') AS INT) > 29

Remember that these JSON fields are not part of an index and the above will perform poorly if you are working with large amounts of data.

That is it!

I hope this helps someone out there, let me know if it did down in the comments below :)