How can I go about replacing a number array inside a json string in SQL?

Question

The data in SQL contains a lengthy JSON string in a cell similar to this:

{
  "Name": "Example",
  "Results": [
    {
      "ResultId": 0,
      "AnswerIds": "[1,2,33,4,5]"
    },
    {
      "ResultId": 1,
      "AnswerIds": "[2,3,4,55,6]"
    }
  ]
}

I have a list of replacement AnswerIds: Replace all 2 with 7, all 3's with 8's

How can I go about making a script for this?

I'm able to isolate the AnswerIds using crossapply and JSON_Query, but not sure how to go about replacing several changes in one array.

For a T-SQL based solution, you can get an idea from Remove element from JSON array for whole table. While that question is about removing array members, the answer code can be tweaked to update array members instead. You could use Json_modify to update the entire array at once - so, for replacing all 2 with 7, you'll have to write something like se @json = JSON_MODIFY(@json,'$.Results[0].AnswerIds',JSON_QUERY('[1,7,33,4,5]')) — Zohar Peled
– Zohar Peled, Commented Aug 22, 2019 at 20:49
While this probably can be solved using T-SQL, the JavaScript solution Sylens shows in his answer is much simpler and probably have a much better performance than anything you can get out of the box in T-SQL. If you don't mind the round trip, I would take the JavaScript solution with both hands. — Zohar Peled
– Zohar Peled, Commented Aug 22, 2019 at 21:09

Sylens · Accepted Answer · 2019-08-22 18:55:38Z

const json = {
  "Name": "Example",
  "Results": [
    {
      "ResultId": 0,
      "AnswerIds": "[1,2,33,4,5]"
    },
    {
      "ResultId": 1,
      "AnswerIds": "[2,3,4,55,6]"
    }
  ]
}

json.Results.forEach((itm, index)=> {
   const arr = JSON.parse(itm.AnswerIds);
   const replacedArray = arr.map(num=>{
      if(num === 2) return 7;
      if(num === 3) return 8;
      return num;
   });
   json.Results[index].AnswerIds = JSON.stringify(replacedArray);
})

console.log(json);

This is what I have done, Take the json.Results array and iterate it with a forEach loop.

You can then access the AnswerIds object of each result.

Since the AnswerIds value is a string, we first convert it to an array.

Then we loop throught that array using a map, and do the replacements.

You might want to read up on JS maps, and JS foreach, JSON.parse, JSON.stringify

Alan Burstein · Accepted Answer · 2019-08-22 21:34:29Z

SQL Server 2016 has JSON support but you did not specify your SQL version. Using DelimitedSplit8k you can do this:

-- SAMPLE STRING
DECLARE @string VARCHAR(1000) = 
'{
  "Name": "Example",
  "Results": [
    {
      "ResultId": 0,
      "AnswerIds": "[1,2,33,4,5]"
    },
    {
      "ResultId": 1,
      "AnswerIds": "[2,3,4,55,6]"
    }
  ]
}';

-- SOLUTION
SELECT NewJSON = 
(
  SELECT
    IIF(i.pos = 0,IIF(i.pos>0 AND sub.string IN(2,3), x.x, sub.string),
      IIF(s2.ItemNumber=1,'"[','')+IIF(i.pos>0 AND sub.string IN(2,3),x.x,sub.string)) +
    IIF(s2.ItemNumber>LEAD(s2.ItemNumber,1) OVER (ORDER BY s.ItemNumber,s2.ItemNumber),']"',
      IIF(i.pos = 0,'',','))
  FROM        dbo.delimitedsplit8k(REPLACE(@string,CHAR(13),''),CHAR(10)) AS s
  CROSS APPLY (VALUES(CHARINDEX('"AnswerIds": "',s.item)))                AS i(pos)
  CROSS APPLY (VALUES(SUBSTRING(s.item, i.pos+14, 8000)))                 AS ss(item)
  CROSS APPLY dbo.delimitedsplit8k(ss.item,IIF(i.pos=0,CHAR(0),','))      AS s2
  CROSS APPLY (VALUES(IIF(i.pos=0,s.item,
                 REPLACE(REPLACE(s2.item,']"',''),'[',''))))              AS sub(string)
  CROSS APPLY (VALUES(REPLACE(REPLACE(sub.string,'2',7),'3',8)))          AS x(x)
  ORDER BY    s.ItemNumber, s2.ItemNumber
  FOR XML PATH('')
);

Returns:

{
  "Name": "Example",
  "Results": [
    {
      "ResultId": 0,
      "AnswerIds": "[1,7,33,4,5]"
    },
    {
      "ResultId": 1,
      "AnswerIds": "[7,8,4,55,6]"
    }
  ]
}

Collectives™ on Stack Overflow

How can I go about replacing a number array inside a json string in SQL?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related