How to avoid json_group_array multil time in SQLite?

34 Views Asked by At

I have a problem when make a Sqlite query, to simply my situation: Let's say I have tables like this

Project            Facility_category         Facility_item              Project_Facility_Relation
--------           --------                  --------                   ---------
id| name           id | name                 id | name                  id | project_id | category_id | item_id

-----------        ---------------------     ---------------------      ----------------
1 | Building A     1  | Relax Outdoor        1 | Square                 1 | 1 | 1 | 1
2 | Apartment B    2  | School               2 | Kid Zone               2 | 1 | 1 | 2
                                             3 | Swimming pool          3 | 1 | 1 | 3
                                             4 | High School A          4 | 1 | 2 | 4
                                             5 | University B           4 | 1 | 2 | 5
                   

Now I made a query like this one:

SELECT pr.id, pr.name,
  (
  SELECT DISTINCT json_group_array(
    json_object('id', qu.id, 
    'name', qu.name, 
    'item',  
        (
            SELECT DISTINCT json_group_array(json_object('id',  ifi.id, 'name', ifi.name))
            FROM Project_Facility_Relation fa
            INNER JOIN Facility_item ifi ON ifi.id = fa.facility_id
            where fa.project_id = pr.id AND fa.qualily_id = qu.id
        )
    ))
  FROM Project_Facility_Relation fa
  INNER JOIN Facility_category qu ON qu.id = fa.qualily_id
  where fa.project_id = pr.id
  ) as Category
FROM Project pr
GROUP BY pr.id

The problem is in my result, Category column run multile time based on how many Facility_category exist in Project_Facility_Relation. In this case, "Relax Outdoor" appear 3 times in Facility_category, so in Category result, there's 3 elements duplicate. "School" 2 times.

Project   
--------   
id | name | Category
-----------      
1 |  Building A |  [
  {
    "id": "1",
    "name": "Relax Outdoor",
    "item": [
      {
        "id": "1",
        "name": "Square",
      },
      {
        "id": "2",
        "name": "Kid Zone",
      },
            {
        "id": "3",
        "name": "Swimming pool",
      },
    ]
  },
    {
    "id": "1",
    "name": "Relax Outdoor",
    "item": [
      {
        "id": "1",
        "name": "Square",
      },
      {
        "id": "2",
        "name": "Kid Zone",
      },
            {
        "id": "3",
        "name": "Swimming pool",
      },
    ]
  },
    {
    "id": "1",
    "name": "Relax Outdoor",
    "item": [
      {
        "id": "1",
        "name": "Square",
      },
      {
        "id": "2",
        "name": "Kid Zone",
      },
            {
        "id": "3",
        "name": "Swimming pool",
      },
    ]
  },
  {
    "id": "2",
    "name": "School",
    "item": [
      {
        "id": "4",
        "name": "High School A",
      },
      {
        "id": "5",
        "name": "University B",
      },
    ]
  },
    {
    "id": "2",
    "name": "School",
    "item": [
      {
        "id": "4",
        "name": "High School A",
      },
      {
        "id": "5",
        "name": "University B",
      },
    ]
  }
]

Pls help me, show me where I am wrong? Many thanks

0

There are 0 best solutions below