CODEWITHSUNDEEP
jsonsql-server
user8179431
user8179431

Reputation:

JSON in SQL Server

I have JSON values stored in a column in a SQL Server database:

'[{"attribute":"Name","Age":50,"sort":true,"visible":true},
  {"attribute":"Address","Street":"Wilson street","Country":"United states"}, 
  {"attribute":"Work","Designation":"Developer","Experience":15}]'

We want to remove that entire work attribute and save that in the same column, we will have different no of items in that attribute, here we have only two(designation and Experience), but no of items will vary for each row.

I want to change the above JSON in below format.

'[{"attribute":"Name","Age":50,"sort":true,"visible":true},
  {"attribute":"Address","Street":"Wilson street","Country":"United states"}]'`

Please suggest me the best way to do that.

Upvotes: 0

Views: 105

Answers (3)

anurag verma
anurag verma

Reputation: 26

If you are using the sql server 2016 or higher then you can use the OpenJson() method. Example:

DECLARE @json NVARCHAR(MAX)

SET @json='{"Name":"Anurag","age":25,"skills":["C#","As.Net","MVC","Linq"]}';

SELECT *
FROM OPENJSON(@json);

Upvotes: 1

Jatin Patel
Jatin Patel

Reputation: 2104

Try below approach, bit complex but do the trick. Read inline comments to understand how it works.

CREATE TABLE #temp(ID INT, JSON varchar(1000))

INSERT INTO #temp VALUES(1,'[{"attribute":"Name","Age":40,"sort":true,"visible":true},
                              {"attribute":"Address","Street":"Wilson street","Country":"United states"}]')

INSERT INTO #temp VALUES(2,'[{"attribute":"Name","Age":50,"sort":true,"visible":true},
                                {"attribute":"Address","Street":"Wilson street","Country":"United states"}, 
                                {"attribute":"Work","Designation":"Developer","Experience":15}]')

INSERT INTO #temp VALUES(3,'[{"attribute":"Name","Age":30,"sort":true,"visible":true},
                                {"attribute":"Work","Designation":"Developer","Experience":15},
                                {"attribute":"Address","Street":"New Wilson street","Country":"United states"}]')

INSERT INTO #temp VALUES(4,'[{"attribute":"Work","Designation":"Developer","Experience":15},
                                {"attribute":"Name","Age":30,"sort":true,"visible":true},                                         
                                {"attribute":"Address","Street":"New Wilson street","Country":"United states"}]')

;WITH CTE AS 
(   
    SELECT ID
        ,JSON       
        ,SUBSTRING(JSON,0,CHARINDEX('"attribute":"Work"',JSON)-1) AS JSON_P1    -- Get string before the "Work" attribute.
        ,SUBSTRING(JSON,CHARINDEX('}',JSON,CHARINDEX('"attribute":"Work"',JSON))+1,LEN(JSON)) AS JSON_P2    -- Get string after the "Work" attribute.
    FROM #temp 
    WHERE JSON LIKE '%"attribute":"Work"%' 
)
SELECT ID
    ,JSON
    -- Remove the Comma(',') character used with "Work" attribute.
    ,CASE WHEN CHARINDEX(',',REVERSE(JSON_P1)) = 0 -- In reverse order, When there is no Comma(',') in the first part of string.
            THEN JSON_P1
        WHEN CHARINDEX(',',REVERSE(JSON_P1)) < CHARINDEX('}',REVERSE(JSON_P1)) -- In reverse order, When Comma(',') appears before a closing bracket, remove it.
            THEN REVERSE(STUFF(REVERSE(JSON_P1),CHARINDEX(',',REVERSE(JSON_P1)),1,''))
        ELSE JSON_P1    -- non of above
    END +
    CASE WHEN CHARINDEX(',',REVERSE(JSON_P1)) = 0 -- Check only if no Comma(',') found in the first part of string.
                AND CHARINDEX(',',JSON_P2) < CHARINDEX('{',JSON_P2) -- When Comma(',') appears before an opening bracket in second part of string, remove it.
            THEN STUFF(JSON_P2,CHARINDEX(',',JSON_P2),1,'')
        ELSE JSON_P2    -- non of above
    END AS JSON_Final
FROM CTE

Upvotes: 0

Abhishek
Abhishek

Reputation: 2490

You could try the below string manipulation to achieve your desired output - 

DECLARE @info NVARCHAR(1000) = '[{"attribute":"Name","Age":50,"sort":true,"visible":true},
  {"attribute":"Address","Street":"Wilson street","Country":"United states"}, 
  {"attribute":"Work","Designation":"Developer","Experience":15}]'
SELECT 
REVERSE(SUBSTRING(REVERSE(SUBSTRING(@info, 0, CHARINDEX('"attribute":"Work"',@info)-1)),CHARINDEX(',',REVERSE(SUBSTRING(@info, 0, CHARINDEX('"attribute":"Work"',@info)-1)))+1,
LEN(REVERSE(SUBSTRING(@info, 0, CHARINDEX('"attribute":"Work"',@info)-1)))))+']'

We also have JSON_MODIFY to update or remove a JSON string.

Upvotes: 0

Related Questions