I am joining three tables in SQLite (using SQFLite in Flutter). The query result produces repetitive values for certain columns which is the expected behavior. What i want to achieve is to get the query results in the form of a JSON object/map. But so far i have not been able to achieve it. Here is what i have gotten so far.

Consider these tables:

Districts

id	district_code	name
1	DX1	District 1
2	DX2	District 2

Zones

id	zone_code	name	district_code
1	ZA	Zone A	DX1
2	ZB	Zone B	DX1
3	ZC	Zone C	DX1
4	ZD	Zone D	DX1

Schools

id	school_code	name	zone_code
1	2020-000014	ABC School	ZB
2	2019-000018	BBB School	ZB
3	2022-000005	MMM School	ZA
4	2018-000312	KKK School	ZA
5	2022-000070	TTT School	ZA
6	2022-000022	AAA School	ZC
7	2022-000036	XYZ School	ZC
8	2020-000011	DDD School	ZD

I am using the following raw query using the SQFLite package in Flutter:

SELECT JSON_GROUP_ARRAY(JSON_OBJECT('districtId',districts.id,'districtName',districts.name,'zone',JSON_OBJECT('zoneName',zones.name,'schoolName',schools.name)))
FROM 
districts 
INNER JOIN zones ON districts.district_code = zones.district_code 
INNER JOIN schools ON zones.zone_code = schools.zone_code;

This gives the following results

[
  {
    "districtId": "DX1",
    "districtName": "District 1",
    "zone": {
      "zoneName": "Zone B",
      "schoolName": "ABC School"
    }
  },
  {
    "districtId": "DX1",
    "districtName": "District 1",
    "zone": {
      "zoneName": "Zone B",
      "schoolName": "BBB School"
    }
  },
  {
    "districtId": "DX1",
    "districtName": "District 1",
    "zone": {
      "zoneName": "Zone A",
      "schoolName": "MMM School"
    }
  },
  {
    "districtId": "DX1",
    "districtName": "District 1",
    "zone": {
      "zoneName": "Zone A",
      "schoolName": "KKK School"
    }
  },
  {
    "districtId": "DX1",
    "districtName": "District 1",
    "zone": {
      "zoneName": "Zone A",
      "schoolName": "TTT School"
    }
  },
  {
    "districtId": "DX1",
    "districtName": "District 1",
    "zone": {
      "zoneName": "Zone C",
      "schoolName": "AAA School"
    }
  },
  {
    "districtId": "DX1",
    "districtName": "District 1",
    "zone": {
      "zoneName": "Zone C",
      "schoolName": "XYZ School"
    }
  },
  {
    "districtId": "DX1",
    "districtName": "District 1",
    "zone": {
      "zoneName": "Zone D",
      "schoolName": "DDD School"
    }
  }
]

The query is working as expected and giving the expected results.

But what i am actually trying to achieve is this:

[
  {
    "districtId": "DX1",
    "districtName": "District 1",
    "zones": [
      {
        "zoneName": "Zone B",
        "schools": [
          {
            "schoolName": "ABC School"
          },
          {
            "schoolName": "BBB School"
          }
        ]
      },
      {
        "zoneName": "Zone A",
        "schools": [
          {
            "schoolName": "MMM School"
          },
          {
            "schoolName": "KKK School"
          },
          {
            "schoolName": "TTT School"
          }
        ]
      },
      {
        "zoneName": "Zone C",
        "schools": [
          {
            "schoolName": "AAA School"
          },
          {
            "schoolName": "XYZ School"
          }
        ]
      },
      {
        "zoneName": "Zone D",
        "schools": [
          {
            "schoolName": "DDD School"
          }
        ]
      }
    ]
  }
]

But i don't know how to get this kind of result. Kindly suggest how to achieve it.