Как агрегировать и объединять или объединять в плоский объект Json с массивами?

Question

У меня есть данные, которые выглядят так:

Таблица клиентов

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

Таблица контактов с клиентами

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

Вот результат, который я ищу:

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

Что я пробовал: мне стыдно сказать, что я даже не близок:

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 1

К сожалению, SQL Server перешел на "фургон JSON" немного поздно (поддержка начата только в версии 2016 года), а это значит, что поддержка JSON все еще невелика (хотя то, что он умеет делать, отлично справляется).
Лично я не знаю ни одного встроенного способа создания массива значений JSON в результате запроса
({"Name":["Value1", "Value2"...]}), хотя создать массив пар ключ-значение
(["Name":"Value1", "Name":"Value2"...]) - по крайней мере, не используя FOR JSON пункт.

Однако, поскольку вы работаете с версиями 2017 и Azure, довольно легко создать такие массивы самостоятельно, используя string_agg (В более ранних версиях это немного сложнее - использование for xml path а также stuff для агрегирования строк).

При этом - вот мое предлагаемое решение:

Во- первых, создание и таблицы Заселите образца (Пожалуйста сохранить этот шаг в ваших будущих вопросов):

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

Затем используйте запрос с FOR JSON PATHчтобы получить вывод json.
Хитрость здесь заключается в том, чтобы сгенерировать внутренние массивы путем объединенияCustomerName / CustomerEmail в результате STRING_AGG подзапрос соответствующего столбца в CustomerContactsТаблица.
Обратите вниманиеJSON_QUERYобертка вокруг этих столбцов. Они необходимы для предотвращения выхода SQL Server из"символов в выводе json - сообщив ему, что содержимое является правильным JSON.
Также обратите внимание на использованиеISNULL действовать как LEFT JOIN - вы получите всех клиентов, даже если у них нет соответствующей записи в CustomerContacts Таблица.

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

Результат:

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

Вы можете увидеть живую демонстрацию (к сожалению, вывод json не имеет хорошего отступа, но тем не менее действителен) DB<>Fiddle