JSON - How to append an array to an array in sql

Question

I have a json field in a table that contains an array like this:-

[
  {
    "ID": 11111,
    "Name": "apple",
  },
  {
    "ID": 22222,
    "Name": "orange",
  },
  {
    "ID": 333333,
    "Name": "banana",
  } 
]

I would like to append/concatenate the following json array to this one:-

[
  {
    "ID": 44444,
    "Name": "grape",
  },
  {
    "ID": 55555,
    "Name": "kiwi",
  },
  {
    "ID": 66666,
    "Name": "fig",
  } 
]

So that I end up with this in the table field:-

[
  {
    "ID": 11111,
    "Name": "apple",
  },
  {
    "ID": 22222,
    "Name": "orange",
  },
  {
    "ID": 333333,
    "Name": "banana",
  },
  {
    "ID": 44444,
    "Name": "grape",
  },
  {
    "ID": 55555,
    "Name": "kiwi",
  },
  {
    "ID": 66666,
    "Name": "fig",
  } 
]

i.e. I've added the three new elements to the three existing elements so that i now have a single array with six elements in my table field.

I have been trying to make this work with JSON_MODIFY and have been successful in adding a single element to the array with something like this:-

select JSON_MODIFY(json_field,'append $', JSON_QUERY('{ "ID": 44444, "Name": "grape" }'))

But I cannot make it append more than one element in a single operation and make it look as desired, I've been trying variations of this:-

select JSON_MODIFY(json_field,'append $', JSON_QUERY('[{ "ID": 44444, "Name": "grape" }, { "ID": 55555, "Name": "kiwi" }, { "ID": 66666, "Name": "fig" }]'))

In this particular case, it appended it with the square brackets so the three new elements ended up being a sub-array!

Is it possible to get append multiple elements of one array to another like this? (Am I being really thick and missing something obvious?!?)

Community · Accepted Answer · 2020-06-20 09:12:55Z

6

From the documentation:

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

This would mean looping, something I'd try to avoid...

I'd suggest either a simple string action or de-composition/re-composition:

DECLARE @json1 NVARCHAR(MAX)=
N'[
  {
    "ID": 11111,
    "Name": "apple"
  },
  {
    "ID": 22222,
    "Name": "orange"
  },
  {
    "ID": 333333,
    "Name": "banana"
  } 
]'; 
DECLARE @json2 NVARCHAR(MAX)=
N'[
  {
    "ID": 44444,
    "Name": "grape"
  },
  {
    "ID": 55555,
    "Name": "kiwi"
  },
  {
    "ID": 66666,
    "Name": "fig"
  } 
]';

--This will re-create the JSON out of derived tables

SELECT t.ID,t.[Name]
FROM
(
    SELECT * FROM OPENJSON(@json1) WITH(ID int,[Name] NVARCHAR(MAX)) 
    UNION ALL
    SELECT * FROM OPENJSON(@json2) WITH(ID int,[Name] NVARCHAR(MAX))
) t
FOR JSON PATH;

--This will create a naked array and STUFF() it into the correct position

DECLARE @NakedArray NVARCHAR(MAX)=N',' +
(
    SELECT A.* 
    FROM OPENJSON(@json2)
    WITH(ID int, Name NVARCHAR(MAX)) A
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);

SELECT STUFF(@json1,LEN(@json1)-1,0,@NakedArray);

The naked array you could achieve simply by replacing the [ with a comma and cut away the ] too...

UPDATE: A minimal approach

Try this minimal approach:

SELECT REPLACE(@json1,']',REPLACE(@json2,'[',','));

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 21, 2018 at 16:50

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

5 Comments

mike123 Over a year ago

The minimal approach is very nifty :)

ckerth Over a year ago

Thanks for the heads-up, one never fails to learn something new. (Deleted my answer because yours does the same thing.)

dan0001 Over a year ago

wow, thanks everyone for such quick responses!! i made the post yesterday evening my time then went straight out to dinner, then didn't check it again until i got back in a few hours later thinking no-one would have even read it let alone answered it (i am new to stackoverflow, been using other forums in the past where responses have been a wee bit slower ;-). anyways back to the answers - doh! i totally thought you could do this with json_modify and i was just missing the obvious (even tho the obvious was actually the docs telling me it couldn't be done!).

dan0001 Over a year ago

using union sounds ideal, but i really do like the simplicity of the minimal approach so i will use that and keep union in reserve as i have lots of json crud to do so i am sure it will come in useful :)

Meneghino Over a year ago

The approaches work for simple cases like the one in the question but do not work for arrays of arbitrary objects. The minimal approach is indeed nifty but will fail when the arrays to be combined contain string values with [ or ] and/or inner arrays.

mike123 · Accepted Answer · 2018-12-21 16:57:03Z

2

Try this:

DECLARE @j1 AS NVARCHAR(MAX) = '[
  {
    "ID": 11111,
    "Name": "apple"
  },
  {
    "ID": 22222,
    "Name": "orange"
  },
  {
    "ID": 333333,
    "Name": "banana"
  } 
] ';


DECLARE @j2 AS NVARCHAR(MAX) = '
[
  {
    "ID": 44444,
    "Name": "grape"
  },
  {
    "ID": 55555,
    "Name": "kiwi"
  },
  {
    "ID": 66666,
    "Name": "fig"
  } 
] ';


SELECT * FROM
(
SELECT [ID], [Name] FROM  OPENJSON(@j1) WITH (ID INT, [Name] NVARCHAR(200))
UNION
SELECT  [ID], [Name]  FROM  OPENJSON(@j2) WITH (ID INT, [Name] NVARCHAR(200))
) x
FOR JSON AUTO

edited Dec 21, 2018 at 16:57

answered Dec 21, 2018 at 16:49

mike123

1,55915 silver badges23 bronze badges

4 Comments

Gottfried Lesigang Over a year ago

I voted your's up, as you were faster by seconds, but you might check my last line ;-) this seems to be the easiest ...

dan0001 Over a year ago

ah, i have just discovered that i cannot mark this as an answer as well as Shnugo's, guess that makes sense to only have one answer maximum. i am not sure whose to give the tick to now as both answers have what i would guess is the proper way to do it using union, but i think i should prob leave it as Shnugo's since i will be using the minimal answer - is that the correct way to do it? apologies, i am not sure of the protocol here. in any case, @mike123 many thanks for the quick response as well :)

mike123 Over a year ago

@dan0001 - Shnugo have helped me with the answer. He deserves the credit :)

Meneghino Over a year ago

The approach works for simple cases like the one in the question but do not work for arrays of arbitrary objects.

renegm · Accepted Answer · 2020-01-23 21:32:05Z

0

I came to the same problem. And I found my solution very uncivilized. But it seems there's nothing better.

SELECT CONCAT(STUFF(@json1,LEN(@json1),1,','),STUFF(@json2,1,1,''))

answered Jan 23, 2020 at 21:32

renegm

6202 gold badges6 silver badges12 bronze badges

Comments

Meneghino · Accepted Answer · 2020-04-01 20:24:00Z

The following expands previous solutions to work for the union of JSON arrays of arbitrary objects/arrays/values, even when string values contain the characters [ or ]. However the solution uses the STRING_AGG function, thus requiring SQL Server 2017 and later.

    DECLARE @json1 NVARCHAR(MAX)=
N'[
  {
    "ID": 11111,
    "Name": "apple"
  },
  {
    "ID": 22222,
    "Number": 1234
  },
  {
    "ID": 333333,
    "Names": ["apple", "banana"]
  } 
]'; 
DECLARE @json2 NVARCHAR(MAX)=
N'[
  {
    "Random": "grape"
  },
  [
    123,
    456
  ],
  "f[i]g"
]';

SELECT CONCAT(
    N'[',
        (
        SELECT STRING_AGG(U.[value],N',') WITHIN GROUP (ORDER BY U.ArrayNo ASC,U.[key] ASC)
            FROM
                (
                SELECT 1 AS ArrayNo,[key],[value] FROM OPENJSON(@JSON1)
                UNION ALL
                SELECT 2 AS ArrayNo,[key],[value] FROM OPENJSON(@JSON2)
                ) AS U
            ),
    N']'
    )

Collectives™ on Stack Overflow

JSON - How to append an array to an array in sql

4 Answers 4

UPDATE: A minimal approach

5 Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

UPDATE: A minimal approach

5 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related