CODEWITHSUNDEEP
jsonsql-servert-sql
LoverBugs
LoverBugs

Reputation: 127

Pushing an object to json array in SQL Server

I am stuck with the json arrays into SQL Server. I followed this tutorial https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15, but I cannot figure out how to push to the existing array.

It works fine with an array of strings:

SET @info=JSON_MODIFY(@info, 'append $.skills', 'Azure')

but it seems it doesn't work with objects (or I am missing something here).

This is what I've tried so far :

declare @json nvarchar(max) =  '{
   "changes":[
      {
         "logid":1,
         "changedfield":"site",
         "oldvalue":"0",
         "newvalue":"637049"
      },
      {
         "logid":2,
         "changedfield":"site",
         "oldvalue":"637049",
         "newvalue":"637041"
      }
     ]
}'

select ISJSON(@json) -- 1

select JSON_QUERY(@json, '$.changes')
--displays
--[
--   {
--      "logid":1,
--      "changedfield":"site",
--      "oldvalue":"0",
--      "newvalue":"637049"
--   },
--   {
--      "contactlogid":516870,
--      "changedfield":"site",
--      "oldvalue":"0",
--      "newvalue":"637049"
--   }
--]

-- now I want to push to the changes array

declare @json1 as nvarchar(max) = '{
   "changes":[
      {
         "logid":1,
         "changedfield":"site",
         "oldvalue":"0",
         "newvalue":"637049"
      },
      {
         "logid":2,
         "changedfield":"site",
         "oldvalue":"637049",
         "newvalue":"637041"
      }
     ]
}'

select JSON_MODIFY(@json, 'append $.changes', @json1)

-- the result 

--{
--   "changes":[
--      {
--         "logid":1,
--         "changedfield":"site",
--         "oldvalue":"0",
--         "newvalue":"637049"
--      },
--      {
--         "logid":2,
--         "changedfield":"site",
--         "oldvalue":"637049",
--         "newvalue":"637041"
--      },
--      "{\r\n   \"changes\":[\r\n      {\r\n         \"logid\":1,\r\n         \"changedfield\":\"site\",\r\n         \"oldvalue\":\"0\",\r\n         \"newvalue\":\"637049\"\r\n      },\r\n      {\r\n         \"logid\":2,\r\n         \"changedfield\":\"site\",\r\n         \"oldvalue\":\"637049\",\r\n         \"newvalue\":\"637041\"\r\n      }\r\n\t ]\r\n}"
--   ]
--}

select JSON_MODIFY(@json, 'append $.changes', (select json_query(@json1,'$.changes')))

-- result -- it creates two arrays

--{
--   "changes":[
--      {
--         "logid":1,
--         "changedfield":"site",
--         "oldvalue":"0",
--         "newvalue":"637049"
--      },
--      {
--         "logid":2,
--         "changedfield":"site",
--         "oldvalue":"637049",
--         "newvalue":"637041"
--      },
--      [
--         {
--            "logid":1,
--            "changedfield":"site",
--            "oldvalue":"0",
--            "newvalue":"637049"
--         },
--         {
--            "logid":2,
--            "changedfield":"site",
--            "oldvalue":"637049",
--            "newvalue":"637041"
--         }
--      ]
--   ]
--}

Do you have any idea how to push an object into the existing array?

Upvotes: 1

Views: 903

Answers (2)

Amirhossein Yari
Amirhossein Yari

Reputation: 2316

I could solve your problem, but I don't know I there a better way to do it:

declare @json nvarchar(max) =  '{
   "changes":[
      {
         "logid":1,
         "changedfield":"site",
         "oldvalue":"0",
         "newvalue":"637049"
      },
      {
         "logid":2,
         "changedfield":"site",
         "oldvalue":"637049",
         "newvalue":"637041"
      }
     ]
}'

declare @json1 as nvarchar(max) = '{
   "changes":[
      {
         "logid":3,
         "changedfield":"site",
         "oldvalue":"0",
         "newvalue":"637049"
      },
      {
         "logid":4,
         "changedfield":"site",
         "oldvalue":"637049",
         "newvalue":"637041"
      }
     ]
}'

SELECT @json = JSON_MODIFY(@json, 'append strict $.changes',JSON_QUERY(value,N'$')) FROM OPENJSON(@json1, '$.changes')

PRINT @json

And the result is :

{
       "changes":[
          {
             "logid":1,
             "changedfield":"site",
             "oldvalue":"0",
             "newvalue":"637049"
          },
          {
             "logid":2,
             "changedfield":"site",
             "oldvalue":"637049",
             "newvalue":"637041"
          }
         ,{
             "logid":3,
             "changedfield":"site",
             "oldvalue":"0",
             "newvalue":"637049"
          },{
             "logid":4,
             "changedfield":"site",
             "oldvalue":"637049",
             "newvalue":"637041"
          }]
    }

Upvotes: 3

lptr
lptr

Reputation: 6788

With JSON_MODIFY you can update only one property. If you have to do multiple updates, you can use multiple JSON_MODIFY calls.

Hence, you can only update/append one value in an array with a single JSON_MODIFY() call.

@Json1 has two values in its changes:[] array and you have to use two nested json_modify() calls to append both values into @json:

select JSON_MODIFY(JSON_MODIFY(@json, 'append $.changes', json_query(@json1,'$.changes[0]')), 'append $.changes', json_query(@json1,'$.changes[1]'));

The answer posted by Amirhossein Yari, does the same (multiple JSON_MODIFY() calls) in a slightly different way.

Upvotes: 2

Related Questions