JSON is the is an open standard file format, and data interchange format,JSON uses human-readable text to store and transmit data object. JSON Stands for JavaScript Object Notation.
JSON used when data is sent from a SQL server to a web page and also web page to server.it is easy to understand and “self-describing”. JSON is the better then XML which is lightweight and easier to manipulate compared to XML. json data is written like as name/value pairs, just like JavaScript object properties.
It can easily be used to text and used as a data format by any programming language. There are many types of json format which is returned by sql as sql server json nested array,sql server json array etc.
JSON format
{"firstName":"debin"}
Object Syntax
{ "firstName":"debin", "age":40, "email":null }
Some rules in JSONG Objects as a given below.
- JSON objects are surrounded by curly braces {}.
- Written in key/value pairs.
- Keys always must be string and values always must be valid JSON data type like string, number, object, array, Boolean or null.
- Keys always must be string and values always must be valid JSON data type like string, number, object, array, Boolean or null.
- Each values/key pair is separated by a comma.
Arrays as JSON Objects
[
{"FirstName":"debin","age":40, "email":"[email protected]"},
{"FirstName":"debin","age":40, "email":"[email protected]"},
{"FirstName":"debin","age":40, "email":"[email protected]"},
]
Nested Arrays in JSON Objects
Values in an array can also be another array, or even another JSON object:
myObjectName = {
"FirstName":"John",
"age":30,
"cars": [
{ "FirstName":"Ford", "models":[ "Fiesta", "Focus", "Mustang" ] },
{ "FirstName ":"BMW", "models":[ "125", "X2", "X7" ] },
{ "FirstName ":"Fiat", "models":[ "600", "Panda" ] }
]
}
How to make return JOSN format from sql database.
I have created two different tables “Menu” and “SubMenu” as given below
Create Menu table and INSERT Record in table as given below
CREATE TABLE Menu(
MenuId INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(100) NOT NULL
)
INSERT INTO Menu (Name) VALUES('First Menu'); INSERT INTO Menu (Name) VALUES('Second Menu');
When Executing the first row in table, this row generates primary key 1 and when executing second row in table, this row generates primary key 2 as given below image.
Step: 1
Create SubMenu table and INSERT Record in table as given below
CREATE TABLE SubMenu(
SubMenuId INT IDENTITY(1,1)PRIMARY KEY, MenuId INT NOT NULL,--This is foreign key, Name NVARCHAR(100)NOT NULL )
--Insert statement here
INSERT INTO SubMenu (MenuId,Name) VALUES(1,'First SubMenu 1’);
INSERT INTO SubMenu (MenuId,Name) VALUES(1,'First SubMenu 2');
INSERT INTO SubMenu (MenuId,Name) VALUES(2,'Second SubMenu 1);
INSERT INTO SubMenu (MenuId,Name) VALUES(2,'Second SubMenu 2');
After executing the rows, and display records in sql server as given below
Step: 2
SQL Server 2016 has FOR JSON clause that formats query result directly in query/stored procedure
For return in JSON format Create Script like
SELECT M.Name, ( SELECT SU.Name FROM SubMenu AS SU WHERE SU.MenuId = M.MenuId FOR JSON PATH ) AS SubMenuList
FROM Menu AS M FOR JSON PATH,ROOT('MenuList')
OUTPUT:
For the above content we are going to clarify that we have returned json format from sql server where “Menu” and “SubMenu” in the sql server are created. As in the above example I have tried to return nested json values from sql server.
Comments