Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- JSON_QUERY Demonstration to extract an object or array from the JSON data
DECLARE @json nvarchar(max)
SET @json =N'
{
"Person": [
{
"Name": {
"FirstName": "Catherine",
"LastName": "Abel"
},
"Address": {
"State": "VA",
"Zip": "24153"
}
}
]
}'
IF (ISJSON(@json) = 1)
PRINT 'It is a Valid JSON' --Valid JSON
ELSE
PRINT 'It is an Invalid JSON string'
SELECT JSON_QUERY(@json, '$.Person');
-- Extract object/array value from a JSON text
SELECT JSON_QUERY(@json, '$.Person[0].Address');
-- Returns NULL if the path does not exist
SELECT JSON_QUERY(@json, '$.Person[0].Age');
-- lax option is by default and returns NULL if there is a problem
SELECT JSON_QUERY(@json, 'lax $.Person[0].Age');
-- strict option raises an error if there is a problem
SELECT JSON_QUERY(@json, 'strict $.Person[0].Age');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment