Create Nested JSON Arrays using FOR JSON PATH

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.

1. JSON objects are surrounded by curly braces {}.
2. Written in key/value pairs.
3. Keys always must be string and values always must be valid JSON data type like string, number, object, array, Boolean or null.
4. Keys always must be string and values always must be valid JSON data type like string, number, object, array, Boolean or null.
5. 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.