Replacing data in JSON using SQL query

Question

The below JSON data is one of the field in the table. In the below JSON data, I need to replace the value which have "Not available" in the expLevel into "Not listed" using SQL query.

"Information": {
        "Name": [],
        "Class": [],
        "Degree": ["Graduate or professional degree"],
        "major": [],
        "skill": [],
        "expLevel": ["0 to 2 years",
                     "Not available",
                     "3 to 5 years"],
        "certificationtype": ""
    }

I have tried this:

update sr set filter = replace(filter, '"Not available"', '"available" , "listed"')
from sharedreports_check sr 
where filter like '%"expLevel":[[]"%Not available%"%'

But it is not worked.

Please let me know what will be the SQL query to replace it.

FYI , Now Json is supported in sql server 2016.Now we can query on json type just as we query in xml type. — KumarHarsh
– KumarHarsh, Commented Apr 28, 2016 at 8:13

Abdul Rasheed · Accepted Answer · 2016-04-28 05:41:06Z

2

Try this

update sr set filter = replace(filter, 'Not available', 'Not listed')
from sharedreports_check sr 
where filter like '%expLevel%Not available%'

answered Apr 28, 2016 at 5:41

Abdul Rasheed

6,7794 gold badges36 silver badges50 bronze badges

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

Comments

KumarHarsh · Accepted Answer · 2016-04-28 08:29:31Z

If you can manipulate this json in front end then it is better.

Another option is to create CLR,if it is very frequent work and data get populated from other source .

Try this script,i think it will work.(for test test try few more example). Also there is no need of where clause.

declare @i nvarchar(max)='"Information": {
        "Name": [],
        "Class": [],
        "Degree": ["Graduate or professional degree"],
        "major": [],
        "skill": [],
        "expLevel": ["0 to 2 years",
                     "Not available",
                     "3 to 5 years"],
        "certificationtype": ""
    }'

-- 1st method
select replace(@i, 'Not available', 'Not listed')

-- 2nd method which is more accurate
select replace(substring(col1,0,charindex(']',col1)),'Not available', 'Not listed') from
(select substring (@i,charindex('expLevel"',@i)+len('expLevel"')+1,len(@i)) Col1)t4

Collectives™ on Stack Overflow

Replacing data in JSON using SQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related