How to check json format in SQL?

I have a table with a column of type Nvarchar where json is stored. But the format of some of its columns is wrong. So I get an error when I parse them all.

I want my query to ignore lines that have errors.

SELECT *
FROM RequestLogs
CROSS APPLY OPENJSON(body) WITH(
    user varchar(60) '$.user',
    pass varchar(60) '$.pass'
    ) as a

It gives this error when I run on all lines:

JSON text is not properly formatted. Unexpected character ‘.’ is found at position 0.

, but it executes correctly on the top 10.

Answer

If I follow you correctly, you could use ISJSON()to check the validity of body before passing it to OPENJSON():

SELECT *
FROM (SELECT * FROM RequestLogs WHERE ISJSON(body) = 1) as r
CROSS APPLY OPENJSON(body) WITH(
    user varchar(60) '$.user',
    pass varchar(60) '$.pass'
) as a

This filters out rows whose content is invalid. If you want to retain them, and return a null result instead, then:

SELECT r.*, a.*
FROM RequestLogs r
CROSS APPLY (VALUES(CASE WHEN WHERE ISJSON(r.body) = 1 THEN r.body END)) as x(body)
CROSS APPLY OPENJSON(x.body) WITH(
    user varchar(60) '$.user',
    pass varchar(60) '$.pass'
) as a

And, of course, you can identify the offending rows like so:

SELECT body
FROM RequestLogs 
WHERE ISJSON(body) = 0