In this post, let us see some examples on handling JSON data in SQL Server using JSON_QUERY, JSON_VALUE
Consider below sample JSON data, let us check whether it is valid :
--Valid 1, Invalid 0
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgex1YCdgwbVR2-r00b_7p932AviDZKGvuu2Od5rX1nHLm4TlrqkZZfey-UP1w9X8GOVJgfAkr57207eX7G7JUmoovpyGBmzhePATr3RP-dKxhbULIp6XF6LbRCbRA_LdnCKuaViuxBLj1Y/s640/j5.png)
Using JSON_QUERY, we can hold chunk / fragment of JSON data
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLS52-rijGb02gXw9w-s-GEslLHrbTuaCokh3iTisGqvK4gqVSudQk_b9NvTRevoG2gniOIwqExd0Mc3dzAjM4GyiqL7IUGwtR2fEXOWqIMTP6X8gZdLQVd4x5PvGiiUv7aHWSOzTXkrMF/s640/j6.png)
Above query will throw error, as path name SQL Server has space and it needs to be enclosed within double quotes as shown in below image :
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcHVqIMV0o8OhdBnmXf8H9fq3hGYv6VYZT4h7oWSYvwWk49IUF5KtaSu6MespqrahgmXNq8_IJgNS5Nh7k9Iwb-W06ZH3XZhxiagH-UYHX6Wkn2FPFq1sBqPaBpD_1k71Z7pxuuwTQHz1X/s640/j7.png)
JSON_VALUE can be used to hold a single value from JSON text.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIgcpIeJxEbM-cOp2qcvmHs7p0K6W9KTN1bo1dIg3hCUrzp9DyxOHZgyGL2ha9PkKZjXOdtxJqG6KyLD0OkTIm5ZFDoyXkgKh7NnRJSpF4XehuwCb4TuUi5QidCpj_Dc7SyNeacwrJxk4V/s640/j8.png)
What is lax and strict ?
lax - returns NULL if the path expression is incorrect / path name is invalid
strict - returns error if the path expression is incorrect / path name is invalid
In below example, I have changed path name YearofRelease to YearsofRelease,
let us see how lax and strict behaves
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvYTvf82MVnIm3xRhy50zxdjnZQaziIi0YF9vx2kfD_iMwh7AA1_ozzZn_Ffyw4GxVQ-yU0QFLvGD4BLftou7L0xD-Rrv-P1glxPU_k_qi_obh_IcPSH3x0rEByvZQR0EJYkz8tAHohqxA/s640/j9.png)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBgRJAFma4GNIDJtF3c-8uaNV0J0-jGQa8yw7iwJKRF6jpZP6WH24-CRmCp33WEDCYC6TYSg2LV7-aOsKlRoquBEqLQQKp6IRyzohgrA3m_aOZd7Ea0kLH9ttXo4bbpyU1mtVHjWB1DjZs/s640/j10.png)
If JSON text has duplicate path names, we can handle that using key value. Key value starts from 0.
Below example shows on handling duplicate path names based on position:
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHu7mus8wryqmmXEnTNeFCMwPwgYcAjit9xZRwMVpJ2J_G3q7dqSl2aMD76Wq7vTnjLMelZhWyAuaO1bjeCdLnXrKkJXBnNDw9tEm_srz1qgkYG_lWJEswTDprHpYhWpPl7evzjwPXMFbY/s640/j11.png)
Consider below sample JSON data, let us check whether it is valid :
{"MSSQL": { "SQL Server":{ "Version":"6.0", "CompatibilityLevel":60, "CodeName":"SQL95" }, "YearofRelease":"1995" } }
DECLARE @Json_data NVARCHAR(MAX) = ' {"MSSQL": { "SQL Server":{ "Version":"6.0", "CompatibilityLevel":60, "CodeName":"SQL95" }, "YearofRelease":"1995" } }' SELECT ISJSON(@Json_data) ValidJsonText
--Valid 1, Invalid 0
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgex1YCdgwbVR2-r00b_7p932AviDZKGvuu2Od5rX1nHLm4TlrqkZZfey-UP1w9X8GOVJgfAkr57207eX7G7JUmoovpyGBmzhePATr3RP-dKxhbULIp6XF6LbRCbRA_LdnCKuaViuxBLj1Y/s640/j5.png)
--Inserting valid JSON data into table DROP TABLE IF EXISTS JSON_Data CREATE TABLE JSON_Data (Id INT IDENTITY(1,1), Json_Col NVARCHAR(MAX)) INSERT JSON_Data SELECT '{"MSSQL": { "SQL Server":{ "Version":"6.0", "CompatibilityLevel":60, "CodeName":"SQL95" }, "YearofRelease":"1995" } }'
Using JSON_QUERY, we can hold chunk / fragment of JSON data
SELECT JSON_QUERY(Json_Col,'$.MSSQL.SQL Server') As JSON_Fragment FROM JSON_Data
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLS52-rijGb02gXw9w-s-GEslLHrbTuaCokh3iTisGqvK4gqVSudQk_b9NvTRevoG2gniOIwqExd0Mc3dzAjM4GyiqL7IUGwtR2fEXOWqIMTP6X8gZdLQVd4x5PvGiiUv7aHWSOzTXkrMF/s640/j6.png)
Above query will throw error, as path name SQL Server has space and it needs to be enclosed within double quotes as shown in below image :
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcHVqIMV0o8OhdBnmXf8H9fq3hGYv6VYZT4h7oWSYvwWk49IUF5KtaSu6MespqrahgmXNq8_IJgNS5Nh7k9Iwb-W06ZH3XZhxiagH-UYHX6Wkn2FPFq1sBqPaBpD_1k71Z7pxuuwTQHz1X/s640/j7.png)
JSON_VALUE can be used to hold a single value from JSON text.
SELECT JSON_VALUE(Json_Col,'$.MSSQL.YearofRelease') As JSON_Val FROM JSON_Data
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIgcpIeJxEbM-cOp2qcvmHs7p0K6W9KTN1bo1dIg3hCUrzp9DyxOHZgyGL2ha9PkKZjXOdtxJqG6KyLD0OkTIm5ZFDoyXkgKh7NnRJSpF4XehuwCb4TuUi5QidCpj_Dc7SyNeacwrJxk4V/s640/j8.png)
What is lax and strict ?
lax - returns NULL if the path expression is incorrect / path name is invalid
strict - returns error if the path expression is incorrect / path name is invalid
In below example, I have changed path name YearofRelease to YearsofRelease,
let us see how lax and strict behaves
--Returns NULL SELECT JSON_VALUE(Json_Col,'lax $.MSSQL.YearsofRelease') As JSON_Val FROM JSON_Data --Returns Error SELECT JSON_VALUE(Json_Col,'strict $.MSSQL.YearsofRelease') As JSON_Val FROM JSON_Data
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvYTvf82MVnIm3xRhy50zxdjnZQaziIi0YF9vx2kfD_iMwh7AA1_ozzZn_Ffyw4GxVQ-yU0QFLvGD4BLftou7L0xD-Rrv-P1glxPU_k_qi_obh_IcPSH3x0rEByvZQR0EJYkz8tAHohqxA/s640/j9.png)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBgRJAFma4GNIDJtF3c-8uaNV0J0-jGQa8yw7iwJKRF6jpZP6WH24-CRmCp33WEDCYC6TYSg2LV7-aOsKlRoquBEqLQQKp6IRyzohgrA3m_aOZd7Ea0kLH9ttXo4bbpyU1mtVHjWB1DjZs/s640/j10.png)
If JSON text has duplicate path names, we can handle that using key value. Key value starts from 0.
Below example shows on handling duplicate path names based on position:
DECLARE @Json_datas NVARCHAR(MAX) = ' {"MSSQL": { "SQLServer":[ {"Version":"6.0","CodeName":"SQL95" }, {"Version":"2014","CodeName":"Hekaton" } ]}}' SELECT ISJSON(@Json_datas) ValidJsonText SELECT JSON_VALUE(@Json_datas,'$.MSSQL.SQLServer[0].Version') As JSON_Val SELECT JSON_VALUE(@Json_datas,'$.MSSQL.SQLServer[1].Version') As JSON_Val
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHu7mus8wryqmmXEnTNeFCMwPwgYcAjit9xZRwMVpJ2J_G3q7dqSl2aMD76Wq7vTnjLMelZhWyAuaO1bjeCdLnXrKkJXBnNDw9tEm_srz1qgkYG_lWJEswTDprHpYhWpPl7evzjwPXMFbY/s640/j11.png)
No comments:
Post a Comment