[SQL] Saving JSON file to SQL Server Database tables

I am having a nested JSON file as shown below (where condition and rules can be nested to multiple levels)

{
"condition": "and",
"rules": [
{
"field": "26",
"operator": "=",
"value": "TEST1"
},
{
"field": "36",
"operator": "=",
"value": "TEST2"
},
{
"condition": "or",
"rules": [
{
"field": "2",
"operator": "=",
"value": 100
},
{
"field": "3",
"operator": "=",
"value": 12
},
{
"condition": "or",
"rules": [
{
"field": "12",
"operator": "=",
"value": "CA"
},
{
"field": "12",
"operator": "=",
"value": "AL"
}
]
}
]
}
]
}


I want to save this JSON (conditon and rules fields in json file can be nested to multiple levels) in to SQL Server Tables and later wanted to construct the same JSON from these created tables. How can i do this ? From these table i am planning to get other json formats also that is why decided to split the json to table columns.

I think need to create a recursive sql function to do same.

i have created following tables to save the same json .

CREATE TABLE [Ruleset]
([RulesetID] [BIGINT] IDENTITY(1, 1) NOT NULL PRIMARY KEY,
[Condition] [VARCHAR](50) NOT NULL,
[ParentRuleSetID] [BIGINT] NULL
);
GO
CREATE TABLE [Rules]
([RuleID] [BIGINT] IDENTITY(1, 1) NOT NULL PRIMARY KEY,
[Fields] [VARCHAR](MAX) NOT NULL,
[Operator] [VARCHAR](MAX) NOT NULL,
[Value] [VARCHAR](MAX) NOT NULL,
[RulesetID] [BIGINT] NULL
FOREIGN KEY REFERENCES [Ruleset](RulesetID)
);


insert script as follows,

INSERT INTO [Ruleset] values
('AND',0),
('OR',1),
('OR',2)

INSERT INTO [Rules] values
('26','=','TEST1',1),
('364','=','TEST2',1),
('2','=','100',2),
('3','=','12',2),
('12','=','CA',3),
('12','=','AL',3)


Will these tables are enough? Will be able to save all details?

Attaching the values that i have added to these tables manually.



How can i save this JSON to these table and later will construct the same JSON from these tables via stored procedure or queries ?

please provide suggestions and samples!

Continue reading...