Is there any way to build a JSON that looks like this using SQL Server 2019?

I have this SQL Query:

SELECT
    c.customer_name as name,
    c.customer_ssn as social_number,
    c.telephone as telephone,
    c.email as email,
    u.user_name as name
FROM
    Customer c
LEFT OUTER JOIN
    User u ON c.id_customer = u.id_customer
WHERE 
    c.id_customer = 2
FOR JSON PATH

SELECT * FROM Customer WHERE ID_CUSTOMER = 2
SELECT * FROM User WHERE ID_Customer = 2

This query returns this JSON :

[
    { 
      "name": "blablabla",
      "social": "123123123",
      "telephone": "91123123",
      "email": "blabla@gmail.com",
      "name":"blabla"
    }
]

Is there any way to get a JSON that looks like this:

{
  "name": "blabla",
  "social_number": "123123123",
  "emails": [
    {
      "email": "blabla@gmail.com"
    },
    {
      "email": "blablabla@gmail.com"
    }
  ],
  "telephones": [
    {
      "number": "(604)6622141"
    },
    {
      "number": "(652) 2123223"
    }
  ],

Thank you for the help!

I'm still new to coding and StackOverflow... sorry for any mistakes!

Solution

You can use correlated subqueries, with FOR JSON in each one

SELECT
    c.customer_name as name,
    c.customer_ssn as social_number,
    telephones = (
        SELECT telephone
        FROM telephone t
        WHERE t.id_customer = c.id_customer
        FOR JSON PATH
    ),
    emails = (
        SELECT email
        FROM email e
        WHERE e.id_customer = c.id_customer
        FOR JSON PATH
    ),
    u.user_name as name
FROM
    Customer c
LEFT OUTER JOIN
    User u ON c.id_customer = u.id_customer
WHERE 
    c.id_customer = 2
FOR JSON PATH;

You can also do this as an APPLY

SELECT
    c.customer_name as name,
    c.customer_ssn as social_number,
    t.telephones,
    e.emails,
    u.user_name as name
FROM
    Customer c
LEFT OUTER JOIN
    User u ON c.id_customer = u.id_customer
OUTER APPLY (
        SELECT telephone
        FROM telephone t
        WHERE t.id_customer = c.id_customer
        FOR JSON PATH
) t(telephones)
OUTER APPLY (
        SELECT email
        FROM email e
        WHERE e.id_customer = c.id_customer
        FOR JSON PATH
) e(emails)
WHERE 
    c.id_customer = 2
FOR JSON PATH;