Saving JSON file to SQL Server Database tables

Question

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!

Answer 1

Actually you can declare the column type as NVARCHAR(MAX), and save the json string into it.

Answer 2

As JSON case sensitive please check your schema definition and sample data. I see a discrepancy between the definition of the tables, their contents and your JSON

All scripts tested on MS SQL Server 2016

I used a temporary table variable in this script, but you can do without it. See an example in SQL Fiddle

-- JSON -> hierarchy table
DECLARE @ExpectedJSON NVARCHAR(MAX) = '
{
    "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"
            }
          ]
        }
      ]
    }
  ]
}
'

DECLARE @TempRuleset AS TABLE 
(RulesetID          BIGINT NOT NULL PRIMARY KEY,
 condition          VARCHAR(50) NOT NULL,
 ParentRuleSetID    BIGINT NOT NULL,
 RulesJSON          NVARCHAR(MAX)
)

;WITH ParseRuleset AS (
    SELECT  1 AS RulesetID,
            p.condition,
            p.rules,
            0 AS ParentRuleSetID
    FROM OPENJSON(@ExpectedJSON, '$') WITH (
        condition   VARCHAR(50),
        rules       NVARCHAR(MAX) AS JSON
    ) AS p

    UNION ALL

    SELECT  RulesetID + 1,
            p.condition,
            p.rules,
            c.RulesetID AS ParentRuleSetID
    FROM ParseRuleset AS c
        CROSS APPLY OPENJSON(c.rules) WITH (
            condition   VARCHAR(50),
            rules       NVARCHAR(MAX) AS JSON
        ) AS p
    where
        p.Rules IS NOT NULL
)

INSERT INTO @TempRuleset (RulesetID, condition, ParentRuleSetID, RulesJSON)
SELECT  RulesetID,
        condition,
        ParentRuleSetID,
        rules
FROM ParseRuleset

 -- INSEERT INTO Ruleset ...
SELECT RulesetID,
        condition,
        ParentRuleSetID,
        RulesJSON
FROM @TempRuleset

-- INSERT INTO Rules ...
SELECT  RulesetID,
        field,
        operator,
        value
FROM @TempRuleset tmp
     CROSS APPLY OPENJSON(tmp.RulesJSON) WITH (
                field       VARCHAR(MAX),
                operator    VARCHAR(MAX),
                value       VARCHAR(MAX)
             ) AS p
WHERE   p.field IS NOT NULL

SQL Fiddle

Hierarchy tables -> 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,
 field      VARCHAR(MAX) NOT NULL,
 operator   VARCHAR(MAX) NOT NULL,
 value      VARCHAR(MAX) NOT NULL,
 RulesetID  BIGINT NULL FOREIGN KEY REFERENCES Ruleset(RulesetID)
);

INSERT INTO Ruleset values  
    ('and',0),
    ('or',1),
    ('or',2) 

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

-- hierarchy table -> JSON
;WITH GetLeafLevel AS 
(
    SELECT  Ruleset.RulesetID,
            Ruleset.condition,
            Ruleset.ParentRuleSetID,
            1 AS lvl,
            (   SELECT  field,
                        operator,
                        value
                FROM    Rules
                WHERE   Rules.RulesetID = Ruleset.RulesetID
                FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER 
            ) AS JSON_Rules
    FROM    Ruleset
    WHERE   ParentRuleSetID = 0
    UNION ALL
    SELECT  Ruleset.RulesetID,
            Ruleset.condition,
            Ruleset.ParentRuleSetID,
            GetLeafLevel.lvl + 1,
            (   SELECT  field,
                        operator,
                        value
                FROM    Rules
                WHERE   Rules.RulesetID = Ruleset.RulesetID
                FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER 
            )
    FROM    Ruleset
            INNER JOIN GetLeafLevel ON Ruleset.ParentRuleSetID = GetLeafLevel.RulesetID
),
-- SELECT * FROM GetLeafLevel       -- debug 
ConcatReverseOrder AS 
(
    SELECT  GetLeafLevel.*,
            CONCAT('{"condition":"',
                    GetLeafLevel.condition,
                    '","rules":[',
                    GetLeafLevel.JSON_Rules,
                    ']}'
                    ) AS js
    FROM    GetLeafLevel
    WHERE   GetLeafLevel.lvl = (SELECT MAX(lvl) FROM GetLeafLevel)
    UNION ALL
    SELECT  GetLeafLevel.*,
            CONCAT('{"condition":"',
                            GetLeafLevel.condition,
                            '","rules":[',
                            GetLeafLevel.JSON_Rules,
                            ',',
                            ConcatReverseOrder.js,
                            ']}'
                            ) AS js
    FROM    GetLeafLevel
            INNER JOIN ConcatReverseOrder ON GetLeafLevel.RuleSetID = ConcatReverseOrder.ParentRuleSetID 
)
-- SELECT * FROM ConcatReverseOrder     -- debug 

SELECT  js
FROM    ConcatReverseOrder
WHERE   ParentRuleSetID = 0

SQL Fiddle