ISJSON, JSON_VALUE, JSON_QUERY and JSON_MODIFY in Sql

SQL Server 2017, provides us four JSON function ISJSON, JSON_VALUE, JSON_QUERY and JSON_MODIFY to validate or change JSON text or to extract simple or complex values from JSON string.

Today in this article we will learn each of JSON function with example one by one.

ISJSON:   ISJSON, function is used to check whether a given string contains valid JSON or not. Valid returns 1 invalid returns 0 and null will returns null.

Syntax:
ISJSON ( expression ) 

expression: The given string to test.

Example:

OUTPUT:
ValidJSON
-----------
1

(1 row affected)

ValidJSON
-----------
0

(1 row affected)

ValidJSON
-----------
NULL

(1 row affected)

JSON_VALUE: JSON_VALUE, function is used to extract an object or an array from a JSON string instead of a scalar value. function will return type nvarchar(4000). If the value exceeds more than 4000 characters it will returns null or error.

Syntax:
JSON_VALUE ( expression , path )  

expression: An expression. Typically the name of a variable or a column that contains JSON text of a table.

path: A JSON path that specifies the property to extract, which is a string begins with $. sign.

OUTPUT:
information
(1 row affected)

OUTPUT:
Color1      Color2  RGBA1   RGBA2     RGBA3
----------- ------- ------- --------- -----
black       white   255     1         0

(1 row affected)

JSON_VALUE: JSON_VALUE, is used to extracts a scalar value from a JSON string instead of an object or an array.

Syntax:
JSON_QUERY ( expression [ , path ] )

expression: An expression. Typically the name of a variable or a column that contains JSON text of a table.

path: A JSON path that specifies the property to extract, which is a string begins with $. sign.

OUTPUT:
{"info":[{"color":"black",
		"category":"hue",
		"type":"primary",
		"code":	{"rgba": [255,255,255,1],
        "hex": "#000"}},
		{"color":"white",
      "category":"value",
      "code":{
        "rgba":[0,0,0,1],
        "hex":"#FFF"}}]}

(1 row affected)

OUTPUT:
[{"color":"black",
		"category":"hue",
		"type":"primary",
		"code":	{"rgba": [255,255,255,1],
        "hex": "#000"}},
		{"color":"white",
      "category":"value",
      "code":{
        "rgba":[0,0,0,1],
        "hex":"#FFF"}}]

(1 row affected)

OUTPUT:
First
--------------------------------------------
{"color":"black",
		"category":"hue",
		"type":"primary",
		"code":	{"rgba": [255,255,255,1],
        "hex": "#000"}}

(1 row affected)

Second
---------------------------------------------
{"color":"white",
      "category":"value",
      "code":{
        "rgba":[0,0,0,1],
        "hex":"#FFF"}}

(1 row affected)

OUTPUT:
color
--------------------
NULL

(1 row affected)

Code
---------------------------
{"rgba": [255,255,255,1],
        "hex": "#000"}

(1 row affected)

Code
--------------------
[255,255,255,1]

(1 row affected)

Code
----------------------
NULL

(1 row affected)

JSON_MODIFY:  JSON_MODIFY, function is used to updates the value of a property in a JSON string and returns the updated JSON string. it returns the updated value of expression as properly formatted JSON text.

Syntax:
JSON_MODIFY ( expression , path , newValue )

expression: An expression. Typically the name of a variable or a column that contains JSON text of a table.

path: A JSON path that specifies the property to extract, which is a string begins with $. sign.

path syntax:

[append] [ lax | strict ] $.

append: Optional modifier that specifies that the new value should be appended to the array referenced by .

lax: Specifies that the property referenced by does not have to exist. If the property is not present, JSON_MODIFY tries to insert the new value on the specified path. Insertion may fail if the property can't be inserted on the path. If you don't specify lax or strict, lax is the default mode.

strict: Specifies that the property referenced by must be in the JSON expression. If the property is not present, JSON_MODIFY returns an error.

: Specifies the path for the property to update.

OUTPUT:
{"info":[{"color":"Blue",
		"category":"hue",
		"type":"primary",
		"code":	{"rgba": [255,255,255,1],
        "hex": "#000"}},
		{"color":"white",
      "category":"value",
      "code":{
        "rgba":[0,0,0,1],
        "hex":"#FFF"}}]}

(1 row affected)

OUTPUT:
{"info":[{"color":"black",
		"category":"hue",
		"type":"primary",
		"code":	{"rgba": [255,255,255,"127"],
        "hex": "#000"}},
		{"color":"white",
      "category":"value",
      "code":{
        "rgba":[0,0,0,1],
        "hex":"#FFF"}}]}

(1 row affected)

Following example will append value for property of JSON string using lax.

OUTPUT:
{"info":[{"color":"black",
		"category":"hue",
		"type":"primary",
		"code":	{"rgba": [255,255,255,1],
        "hex": "#000"}},
		{"color":"white",
      "category":"value",
      "code":{
        "rgba":[0,0,0,1,0.5],
        "hex":"#FFF"}}]}

(1 row affected)

Following example will append property of JSON string using lax.

OUTPUT:

{"info":[
	{"color":"black","category":"hue","type":"primary", "code": {"rgba": [255,255,255,1],"hex": "#000"}},
	{"color":"white","category":"value","code":{"rgba":[0,0,0,1],"hex":"#FFF"},"type":"secondry"}
	]
}

Following example will throw an exception if given properly is not present with in JSON string.

OUTPUT:
Msg 13608, Level 16, State 2, Line 14
Property cannot be found on the specified JSON path.

In next article, we will learn one more powerful OPENJSON function. OPENJSON is a table-valued function, Used to convert JSON object string into table with columns.

To learn more about OPENJSON function click here..

To know more about, How we can convert C# Object in the form of JSON object and store JSONG object stream into SQL Server Database click here.