Monday, August 24, 2020

SQL - Modify Array of JSON

 How to copy one array of JSON object into another array of JSON in SQL. Take this below sample copy array of empIds from one JSON into another JSON


DECLARE  @pArrayOfIntEmps nvarchar(max)='{ "empIds": [' + '1,2'+'] }'

--JSON_Query will return array value from json whereas JSON_VALUE return object value from json

--select JSON_QUERY(@pArrayOfIntEmps,'$.empIds') 

 

DECLARE @info NVARCHAR(max)='{"id":1,"abc":false,"empIds":[3,4] }'

SET @info=  JSON_MODIFY(ISNULL(@info,'{}'),'$.empIds,JSON_QUERY(@pArrayOfIntEmps,'$.empIds'))

select @info

 

--Output {"id":1,"abc":false,"empIds":[1,2] }, this empIds will be replaced by array of int