Problem when fetching the main node and its children, spring data neo4j

Question

Well I have two nodes, one called console which has several other children nodes and another called computer which also has several children, today when I try to search for "all" with spring's neo4j repository, it returns the console object with its children but it also returns this duplicate data, as I will show below, I would like to return only the main node with its children, without them becoming duplicates.

I tried with this query here, which works in the neo4j console, but in spring it doesn't.

@Query("MATCH path=(root:categories)-[:HAS_SUBCATEGORY*0..]->(sub:categories) " +
            "WHERE NOT ()-[:HAS_SUBCATEGORY]->(root) " +
            "WITH root, collect(sub) as subcategories " +
            "RETURN root, subcategories")
    List<CategoryEntity> findCategoriesWithSubcategories();

The return I expected was this:

{
        "id": "29514e97-42fb-4c42-bfc7-6689e48f1c5f",
        "name": "Console",
        "description": "Video games",
        "subCategories": [
            {
                "id": "a054c37c-908d-46e5-ae80-b171dc2271a4",
                "name": "Playstation",
                "description": "video games",
                "subCategories": [
                    {
                        "id": "081d526b-2251-467e-86e4-e37638d04de6",
                        "name": "Playstation5",
                        "description": null,
                        "subCategories": [],
                        "primary": false
                    },
                    {
                        "id": "16e8df4e-06ea-4e52-816a-839ac7bb7201",
                        "name": "Playstation4",
                        "description": null,
                        "subCategories": [],
                        "primary": false
                    }
                ],
                "primary": false
            },
            {
                "id": "35f5484e-a700-403d-862a-4c20ead1eb12",
                "name": "Xbox",
                "description": "video games",
                "subCategories": [
                    {
                        "id": "956b42e9-fe6a-4b0e-a701-eaba86832f7a",
                        "name": "XboxSeries",
                        "description": null,
                        "subCategories": [],
                        "primary": false
                    },
                    {
                        "id": "f2369cd8-6e3f-473f-94c9-d89cf0cb0cdb",
                        "name": "XboxOne",
                        "description": null,
                        "subCategories": [],
                        "primary": false
                    }
                ],
            }
        ],
        "primary": true
    },

and my entity looks like this:

@Node("categories")
public class CategoryEntity {

    @Id
    private String id;

    @Property(name = "name")
    private String name;

    @Property(name = "description")
    private String description;

    @Property(name = "is_primary")
    private boolean isPrimary;

    @Relationship(type = "HAS_SUBCATEGORY", direction = Relationship.Direction.OUTGOING)
    private Set<CategoryEntity> subCategories;

    @Property(name = "created_at")
    private Instant createdAt;

    @Property(name = "updated_at")
    private Instant updatedAt;

    public CategoryEntity() {
    }

    public CategoryEntity(String id, String name, String description, boolean isPrimary, Set<CategoryEntity> subCategories, Instant createdAt, Instant updatedAt) {
        this.id = id;
        this.name = name;
        this.description = description;
        this.isPrimary = isPrimary;
        this.subCategories = subCategories == null ? new HashSet<>() : subCategories;
        this.createdAt = createdAt;
        this.updatedAt = updatedAt;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public boolean isPrimary() {
        return isPrimary;
    }

    public void setPrimary(boolean primary) {
        isPrimary = primary;
    }

    public Set<CategoryEntity> getSubCategories() {
        return subCategories;
    }

    public void setSubCategories(Set<CategoryEntity> subCategories) {
        this.subCategories = subCategories;
    }

    public Instant getCreatedAt() {
        return createdAt;
    }

    public void setCreatedAt(Instant createdAt) {
        this.createdAt = createdAt;
    }

    public Instant getUpdatedAt() {
        return updatedAt;
    }

    public void setUpdatedAt(Instant updatedAt) {
        this.updatedAt = updatedAt;
    }
}

Answer 1

It looks like you are just missing to return the relationship(s) from the database.

@Query("MATCH path=(root:categories)-[r:HAS_SUBCATEGORY*0..]->(sub:categories) " +
            "WHERE NOT ()-[:HAS_SUBCATEGORY]->(root) " +
            "WITH root, collect(r) as rels, collect(sub) as subcategories " +
            "RETURN root, rels, subcategories")

SDN will only map and connect the data if there is a relationship defined between two entries. From the result from the initial query it cannot determine if the root is connected to the subcategory.