Pushing an object to json array in SQL Server

Question

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?

Amirhossein Yari · Accepted Answer · 2020-12-11 20:10:00Z

3

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"
          }]
    }

edited Dec 11, 2020 at 20:10

answered Dec 11, 2020 at 17:49

Amirhossein Yari

2,3363 gold badges29 silver badges42 bronze badges

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

2 Comments

Amirhossein Yari Over a year ago

I added strict to answer, I think you have made mistake in position of strict, it is after append

LoverBugs Over a year ago

I need one column with a default value to be getutcdate(), do you have any idea how to do it?

lptr · Accepted Answer · 2020-12-11 18:04:59Z

2

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.

answered Dec 11, 2020 at 18:04

lptr

6,8582 gold badges10 silver badges16 bronze badges

5 Comments

LoverBugs Over a year ago

this is great answer, but if you add strict before the append it shows an error. JSON path is not properly formatted. Unexpected character 'a' is found at position 7. but still, this is valid json object :o it seems like strict (the validation) for the JSON is bugged? I posted this comment twice, on the first answer and here, it seems like strict validation is bugged!!!!

lptr Over a year ago

[email protected] 'append strict $.changes' and the execution was normal (strict needs to go after the append, it is in the documentation : path has the following syntax: [append] [ lax | strict ] $.<json path> learn.microsoft.com/en-us/sql/t-sql/functions/…

LoverBugs Over a year ago

I need one column with a default value to be getutcdate(), do you have any idea how to do it?

lptr Over a year ago

..@LoverBugs... do you want to add a new datetime column (which has getutcdate() default value) to a table?

LoverBugs Over a year ago

yes, that's correct, but that column to be into the json object

Collectives™ on Stack Overflow

Pushing an object to json array in SQL Server

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related