CODEWITHSUNDEEP
jsonsql-server
Aritra Sarkar
Aritra Sarkar

Reputation: 225

Convert the contents of a SQL Server Column into a JSON Format

I'm having a SQL Server Table with a column named 'Filter'.

Below are the SQL Server Scripts to create a sample table:

CREATE TABLE dbo.TestJSON
(
    TestJSONID INT IDENTITY(1,1),
    [Filter] NVARCHAR(4000)
)

INSERT INTO dbo.TestJSON ([Filter]) 
VALUES ('$WYTS IN (''Control'', ''Machine'', ''Power'', ''DSP'', ''NM'', ''Digital'', ''AI'')')

Now my target is to convert the contents of the column Filter into the following JSON Format:

"conditions":{
    "condition":"AND",
    "rules":[
        {
            "condition":"AND",
            "operator":"IN",
            "value":[
                "Control",
                "Machine",
                "Power",
                "DSP",
                "NM",
                "Digital",
                "AI"
            ],
            "type":"string"
        }
    ]
}

How can I achieve this?

Any help is going to be highly appreciated.

Thanks in advance. :)

Upvotes: 1

Views: 239

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

Here's one option

Example

Select [conditions.condition]='AND'
      ,[conditions.rules] = json_query(
                            (Select condition='AND'
                                   ,operator ='IN'
                                   ,value    = json_query('['+replace(stuff(stuff(Filter,charindex(')',Filter),len(Filter),''),1,charindex('(',Filter),''),'''','"')+']')
                                   ,type     = 'string'
                              For  JSON Path )
                          )
 From  TestJSON
 For JSON Path,Without_Array_Wrapper

Results

 {
  "conditions": {
    "condition": "AND",
    "rules": [
      {
        "condition": "AND",
        "operator": "IN",
        "value": [
          "Control",
          "Machine",
          "Power",
          "DSP",
          "NM",
          "Digital",
          "AI"
        ],
        "type": "string"
      }
    ]
  }
}

If By Chance You Need to Escape the String

Select [conditions.condition]='AND'
      ,[conditions.rules] = json_query(
                            (Select condition='AND'
                                   ,operator ='IN'
                                   ,value    = json_query('['+replace(stuff(stuff(B.S,charindex(')',B.S),len(B.S),''),1,charindex('(',B.S),''),'''','"')+']')
                                   ,type     = 'string'
                              For  JSON Path )
                          )
 From  TestJSON A
 Cross Apply ( values ( string_escape(Filter,'JSON') ) ) B(S)
 For JSON Path,Without_Array_Wrapper

Upvotes: 1

Related Questions