How to aggregate and Join or Union into flat Json object with arrays?

I have data that looks like:

Customers Table

CustomerId  CustomerName CustomerEmail  
------------------------------------------
1           Ben          [e-mail]
2           Robert       [e-mail]
3           Paul         [e-mail]

CustomerContacts Table

CustomerContactId  CustomerId  ContactName   ContactEmail
----------------------------------------------------------
99                 1           Lisa          [e-mail]
98                 3           Jane          [e-mail]
97                 3           Wendy         [e-mail]

Here’s the result I’m looking for:

[
    {
        "CustomerId": 1,
        "Names": [ "Ben","Lisa" ],
        "Emails": [ "[e-mail]","[e-mail]" ]
    },
    {
        "CustomerId": 2,
        "Names": [ "Robert" ],
        "Emails": [ "[e-mail]" ]
    },
    {
        "CustomerId": 3,
        "Names": [ "Paul","Jane","Wendy" ],
        "Emails": [ "[e-mail]","[e-mail]","[e-mail]" ]
    }
]

What I have tried: I’m embarrassed to say I’m not even close:

SELECT 
    Customers.CustomerId,
     STUFF( ISNULL(',' + Customers.CustomerName, '') + ISNULL(',' + CustomerContacts.ContactName, ''),1,1,'') as Names
FROM Customers
FULL JOIN CustomerContacts
ON Customers.CustomerId = CustomerContacts.CustomerId
GROUP BY Customers.CustomerId;

Answer

Unfortunately, SQL Server jumped on the JSON wagon a bit late in the game (started built in support only in 2016 version), which means it’s JSON support is still not great (Though what it does know how to do it does great).
Personally, I don’t know of any built-in way to create a JSON array of values as a result of a query
({"Name":["Value1", "Value2"...]}) though it’s quite easy to generate an array of key-value pairs
(["Name":"Value1", "Name":"Value2"...]) – at least not by using the FOR JSON clause.

However, Since you’re working with 2017 and Azure versions, it is quite easy to generate such arrays yourself, using string_agg (It’s a bit more cumbersome in earlier versions – using for xml path and stuff to aggregate strings).

That being said – here’s my proposed solution:

First, create and populate sample tables (Please save is this step in your future questions):

CREATE TABLE Customers (
    [CustomerId] int, 
    [CustomerName] varchar(6), 
    [CustomerEmail] varchar(16)
);

INSERT INTO Customers ([CustomerId], [CustomerName], [CustomerEmail]) VALUES
(1, 'Ben', '[e-mail]'),
(2, 'Robert', '[e-mail]'),
(3, 'Paul', '[e-mail]');

CREATE TABLE CustomerContacts (
    [CustomerContactId] int, 
    [CustomerId] int, 
    [ContactName] varchar(5), 
    [ContactEmail] varchar(13)
);

INSERT INTO CustomerContacts ([CustomerContactId], [CustomerId], [ContactName], [ContactEmail]) VALUES
(99, 1, 'Lisa', '[e-mail]'),
(98, 3, 'Jane', '[e-mail]'),
(97, 3, 'Wendy', '[e-mail]');

Then, use a query with FOR JSON PATH to get a json output.
The trick here is to generate the inner arrays by concatenating the CustomerName / CustomerEmail with the result of a STRING_AGG subquery of the relevant column in the CustomerContacts table.
Note the JSON_QUERY wrapper around these columns. They are needed to prevent SQL Server to escape the " chars in the json output – by telling it that the content is proper JSON.
Also, note the usage of ISNULL to act as a LEFT JOIN – you’ll get all the customers, even if they don’t have a corresponding record in the CustomerContacts table.

SELECT  C.CustomerId, 
        JSON_QUERY(
            '["' + C.CustomerName + ISNULL('","'+ 
            (
                SELECT STRING_AGG(CC.ContactName, '","') WITHIN GROUP (ORDER BY CustomerContactId)
                FROM CustomerContacts As CC
                WHERE CC.CustomerId = C.CustomerId
            ), '') + '"]'
        ) As Names,
        JSON_QUERY(
            '["' + C.CustomerEmail + ISNULL('","'+ 
            (
                SELECT STRING_AGG(CC.ContactEmail, '","') WITHIN GROUP (ORDER BY CustomerContactId)
                FROM CustomerContacts As CC
                WHERE CC.CustomerId = C.CustomerId
            ), '') + '"]'
        ) As Emails
FROM Customers AS C
FOR JSON PATH

Result:

[
    {
        "CustomerId": 1,
        "Names": ["Ben", "Lisa"],
        "Emails": ["[e-mail]", "[e-mail]"]
    }, {
        "CustomerId": 2,
        "Names": ["Robert"],
        "Emails": ["[e-mail]"]
    }, {
        "CustomerId": 3,
        "Names": ["Paul", "Wendy", "Jane"],
        "Emails": ["[e-mail]", "[e-mail]", "[e-mail]"]
    }
]

You can see a live demo on DB<>Fiddle
(Unfortunately, json output isn’t nicely indented but it’s valid none the less)