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": "[email protected]",
      "name":"blabla"
    }
]

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

{
  "name": "blabla",
  "social_number": "123123123",
  "emails": [
    {
      "email": "[email protected]"
    },
    {
      "email": "[email protected]"
    }
  ],
  "telephones": [
    {
      "number": "(604)6622141"
    },
    {
      "number": "(652) 2123223"
    }
  ],

Thank you for the help!

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

CodePudding user response：

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;