JSON in SQL Server

Question

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.

Milad Rashidi · Accepted Answer · 2018-05-14 19:20:05Z

1

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);

edited May 14, 2018 at 19:20

Milad Rashidi

1,3964 gold badges24 silver badges40 bronze badges

answered May 14, 2018 at 19:09

anurag verma

264 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abhishek · Accepted Answer · 2018-04-12 15:55:45Z

0

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.

answered Apr 12, 2018 at 15:55

Abhishek

2,4901 gold badge24 silver badges35 bronze badges

Comments

Jatin Patel · Accepted Answer · 2018-04-13 06:43:01Z

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

Collectives™ on Stack Overflow

JSON in SQL Server

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related