Convert table into JSON with JSON AUTO

Note: Top is used to limit 3 rows

select top 3 NationalIDNumber from [HumanResources].[Employee] for json auto

Output:
[{"NationalIDNumber":"10708100"},{"NationalIDNumber":"109272464"},{"NationalIDNumber":"112432117"}]

Define Root in the JSON value

select top 3 NationalIDNumber from [HumanResources].[Employee] for json auto, root("root")

output:
{"root":[{"NationalIDNumber":"10708100"},{"NationalIDNumber":"109272464"},{"NationalIDNumber":"112432117"}]}

Use of JSON Value in Oracle

DECLARE @jsonInfo NVARCHAR(MAX)
DECLARE @town NVARCHAR(32)

SET @jsonInfo=N'{"info":{"address":[{"town":"Paris"},{"town":"London"}]}}';

SET @town=JSON_VALUE(@jsonInfo,'$.info.address[0].town'); -- Paris
print @town 

SET @town=JSON_VALUE(@jsonInfo,'$.info.address[1].town'); -- London
print @town 

------------------------------------------

DECLARE @jsonInfo NVARCHAR(MAX)
DECLARE @town NVARCHAR(32)

SET @jsonInfo=N'{"root":[{"NationalIDNumber":"10708100"},{"NationalIDNumber":"109272464"},{"NationalIDNumber":"112432117"}]}';

SET @town=JSON_VALUE(@jsonInfo,'$.root[0].NationalIDNumber'); -- 10708100
print @town 

Filter the data in JSON_VALUE

select columnname from tablename where json_value(columnname, '$.Age') between 25 and 30;

Update the JSON data with JSON_MODIFY

DECLARE @jsonInfo NVARCHAR(MAX)
DECLARE @town NVARCHAR(max)

SET @jsonInfo=N'{"root":[{"NationalIDNumber":"10708100"},{"NationalIDNumber":"109272464"},{"NationalIDNumber":"112432117"}]}';

SET @town=JSON_modify(@jsonInfo,'$.root[0].NationalIDNumber', '233434'); 

print @town 

OUTPUT:

{"root":[{"NationalIDNumber":"233434"},{"NationalIDNumber":"109272464"},{"NationalIDNumber":"112432117"}]}

OR

Date:
{"Name":"RAM","Age":35}

update employee set empdetail = JSON_MODIFY(empdetail,'$.Age',25) where employeeid = 3;

Check the value that its JSON or Not

DECLARE @jsonInfo NVARCHAR(MAX)
DECLARE @town NVARCHAR(max)

SET @jsonInfo=N'{"1"."2"."3"}';


SET @town=ISJSON(@jsonInfo); 

print @town 

SET @town=ISJSON(N'[{"NationalIDNumber":"10708100"},{"NationalIDNumber":"109272464"},{"NationalIDNumber":"112432117"}]')

print @town 

OUTPUT
0   -- means no json
1   -- means JSON