I have a table of items and content items. Each item has one content item.

I would like to select the rows to a JSON array but on the same level as the array I need an identifier field which must not repeat.

Here is an example of what I expect the output to be. At the top level, there is a single identifier field and then an array of items.

{
        "Identifier": "71A08718-4D35-4661-BCB9-DA8BA13F005E",

        "Items": [{
        "RecipientName": "Name1",
        "RecipientSurname": "Surname1",
        "RecipientContactNumber": "10001000",
        "RecipientEmail": "email@email.com",
        "Contents": {
            "ItemDescription": "description 1",
            "ItemQuanity": 1,
            "ItemNetWeightg": 10
        }
    }, {
        "RecipientName": "Name2",
        "RecipientSurname": "Surname2",
        "RecipientContactNumber": "20002000",
        "RecipientEmail": "email2email2.com",
        "Contents": {
            "ItemDescription": "description 1",
            "ItemQuanity": 1,
            "ItemNetWeightg": 10
        }
    }
]
}

But the identifier is repeated in each item. This is what I am getting currently. The identifier is repeated inside each item in the array.

{
    "Items": [{
            "Identifier": "71A08718-4D35-4661-BCB9-DA8BA13F005E",
            "RecipientName": "Name1",
            "RecipientSurname": "Surname1",
            "RecipientContactNumber": "10001000",
            "RecipientEmail": "email@email.com",
            "Contents": {
                "ItemDescription": "description 1",
                "ItemQuanity": 1,
                "ItemNetWeightg": 10
            }
        }, {
            "Identifier": "329CC547-D418-4863-A50C-DFB072B66FE7",
            "RecipientName": "Name2",
            "RecipientSurname": "Surname2",
            "RecipientContactNumber": "20002000",
            "RecipientEmail": "email2email2.com",
            "Contents": {
                "ItemDescription": "description 1",
                "ItemQuanity": 1,
                "ItemNetWeightg": 10
            }
        }
    ]
}

Here is what I have tried.

First some sample data. There are 2 tables. First the items table. This is followed by the content items table. For the example I insert 2 rows into each table. The content items are linked to the items using the OrderNumber column.

DECLARE @Items TABLE (
ID bigint
,Number nvarchar(20)
,Date datetime2
,OASNumber nvarchar(20)
,ContactName nvarchar(50)
,ContactSurname nvarchar(50)
,Mobile nvarchar(20)
,Email nvarchar(50)
,Type nvarchar(50)
)

DECLARE @ContentItems TABLE 
(Id bigint
,OrderNumber nvarchar(50)
,ItemDescription nvarchar(max)
,Quantity int
)

INSERT INTO @Items
SELECT
1,'ON1',GETDATE(),'OAS1','Name1','Surname1','10001000','email@email.com','Sales of Goods'
INSERT INTO @ContentItems
SELECT
1,'ON1','description 1',1

INSERT INTO @Items
SELECT
2,'ON2',GETDATE(),'OAS2','Name2','Surname2','20002000','email2email2.com','Sales of Goods'
INSERT INTO @ContentItems
SELECT
3,'ON2','description 1',1

SELECT * FROM
(
SELECT 
    NEWID() AS Identifier
    ,D.ContactName AS [RecipientName]
    ,D.ContactSurname AS [RecipientSurname]
    ,D.Mobile AS [RecipientContactNumber]
    ,D.Email AS [RecipientEmail]
    ,Item.ItemDescription AS [Contents.ItemDescription]
    ,Item.Quantity AS [Contents.ItemQuanity]
    ,10 AS [Contents.ItemNetWeightg]
FROM @Items D
JOIN @ContentItems Item ON Item.OrderNumber = D.Number
) X
FOR JSON PATH, ROOT('Items')

>Solution :

You need to nest your FOR JSON clauses

SELECT
  NEWID() AS Identifier,
  (
    SELECT 
       i.ContactName AS RecipientName
      ,i.ContactSurname AS RecipientSurname
      ,i.Mobile AS RecipientContactNumber
      ,i.Email AS RecipientEmail
      ,ci.ItemDescription AS [Contents.ItemDescription]
      ,ci.Quantity AS [Contents.ItemQuanity]
      ,10 AS [Contents.ItemNetWeightg]
    FROM Items i
    JOIN ContentItems ci ON ci.OrderNumber = i.Number
    FOR JSON PATH
  ) AS Items
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

db<>fiddle

You can also nest it further, for example if you want Items and ContentItems as nested arrays.