CODEWITHSUNDEEP
sqljsonsql-servert-sqlsql-server-2016
dan0001
dan0001

Reputation: 163

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

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?!?)

Upvotes: 4

Views: 4283

Answers (4)

Meneghino
Meneghino

Reputation: 1021

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']'
    )

Upvotes: -1

renegm
renegm

Reputation: 620

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,''))

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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,'[',','));

Upvotes: 6

mike123
mike123

Reputation: 1559

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

Upvotes: 2

Related Questions