In SQL Server, the ISJSON function is used to determine whether a given string is in the JSON format. It returns 1 (true) if the string contains valid JSON, and 0 (false) if it does not. The function can be particularly useful in scenarios where you need to validate JSON data before attempting further processing.
Syntax
ISJSON ( expression )
- expression: This is the string that you want to validate as JSON.
Example Usage
Here's a simple example of how to use ISJSON in SQL Server:
1. Valid JSON:
DECLARE @jsonValid NVARCHAR(MAX) = '{"name": "John", "age": 30}';
IF ISJSON(@jsonValid) = 1
BEGIN
PRINT 'Valid JSON';
END
ELSE
BEGIN
PRINT 'Invalid JSON';
END
2. Invalid JSON:
DECLARE @jsonInvalid NVARCHAR(MAX) = '{name: John, age: 30}';
IF ISJSON(@jsonInvalid) = 1
BEGIN
PRINT 'Valid JSON';
END
ELSE
BEGIN
PRINT 'Invalid JSON';
END
3. Querying a Table:
If you have a table containing JSON strings, you can filter the rows that contain valid JSON:
CREATE TABLE JsonData (
Id INT PRIMARY KEY,
JsonString NVARCHAR(MAX)
);
INSERT INTO JsonData (Id, JsonString)
VALUES (1, '{"name": "Alice", "age": 25}'),
(2, '{name: Alice, age: 25}'),
(3, '{"items": ["item1", "item2"]}');
SELECT Id, JsonString
FROM JsonData
WHERE ISJSON(JsonString) = 1
In the above examples:
- The first example checks a string that is valid JSON and outputs "Valid JSON".
- The second example checks a string that is not formatted correctly and outputs "Invalid JSON".
- The third example shows how to filter a table to get only the rows with valid JSON strings.
Using ISJSON is a straightforward and efficient way to validate JSON data in SQL Server.