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

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

CodePudding user response：

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;

CodePudding user response：

SQL does not support rows inside rows as you actually want, instead you have to return the entire result set (either as a join or 2 separate datasets) from SQL server then create the objects in your backend. If you are using .net and EF/Linq this is as simple as getting all the parents with an include to also get the children. Other backends will do this in other ways.