Left join add matched rows in child table to an array in parent row (as JSON format)

I have the following two tables:

+----------------------------+
|       Parent Table         |
+----------------------------+
| uuid (PK)                  |
| caseId                     |
| param                      |
+----------------------------+

+----------------------------+
|       Child Table          |
+----------------------------+
| uuid (PK)                  |
| parentUuid (FK)            |
+----------------------------+

My goal is to do a (left?) join and get all matching rows in the child table based on the FK in an array on the parent row and not inside the parent row itself on matching column names (see further down on desired output).

Examples of values in tables:

Parent table:

1. uuid: "10dd617-083-e5b5-044b-d427de84651", caseId: 1, param: "test1"
2. uuid: "5481da7-8b7-22db-d326-b6a0a858ae2f", caseId: 1, param: "test1"
3. uuid: "857dec3-aa3-1141-b8bf-d3a8a3ad28a7", caseId: 2, param: "test1"

Child table:

1. uuid: 7eafab9f-5265-4ba6-bb69-90300149a87d, parentUuid: 10dd617-083-e5b5-044b-d427de84651
2. uuid: f1afb366-2a6b-4cfc-917b-0794af7ade85, parentUuid: 10dd617-083-e5b5-044b-d427de84651

What my desired output should look like:

Something like this query (with pseudo-ish SQL code):

SELECT * 
  FROM Parent_table 
 WHERE caseId = '1' 
  LEFT JOIN Child_table ON Child_table.parentUuid = Parent_table.uuid

Desired output (in JSON)

[
  {
    "uuid": "10dd617-083-e5b5-044b-d427de84651",
    "caseId": "1",
    // DESIRED FORMAT HERE
    "childRows": [
      {
        "uuid": "7eafab9f-5265-4ba6-bb69-90300149a87d",
        "parentUuid": "10dd617-083-e5b5-044b-d427de84651"
      },
      {
        "uuid": "f1afb366-2a6b-4cfc-917b-0794af7ade85",
        "parentUuid": "10dd617-083-e5b5-044b-d427de84651"
      }
    ]
  },
  {
    "uuid": "5481da7-8b7-22db-d326-b6a0a858ae2f",
    "caseId": "1"
  }
]

Answer

You can use nested FOR JSON clauses to achieve this.

SELECT
  p.uuid,
  p.caseId,
  childRows = (
    SELECT
      c.uuid,
      c.parentUuid
    FROM Child_table c
    WHERE c.parentUuid = p.uuid
    FOR JSON PATH
  )
FROM Parent_table p
WHERE p.caseId = '1' 
FOR JSON PATH;

Advertisement

Answer