Return JSON of hierarchical data of varying depths (sport teams in different leagues)

I'm trying to create a JSON-stype output of sport teams from different leagues. The problem I'm having is that the leagues are of varying depths. For example...

  • English Premier League: League > team
  • WNBA: League > conference > team
  • NBA: League > conference > division > team
  • NCAA American football: All of the above at the same time

I can create a query for a league with a fixed number of levels, such as the EPL or NBA, but I'm stuck on leagues like collegiate American football (NCAA FBS), where team nodes are 0, 1, or 2 levels deep. I attached a few files that might help understand the problem:

If this were python or something I could use a loop, but how can I do something similar in native cypher? I know I can't use a recursive query, so perhaps a UNION ALL with 3 subqueries, one for each level? Or using an apoc.case statement?

  • neo4j version: browser with APOC

Welcome to the Neo4j Community!

This is an interesting problem for sure. One way to model this would be to connect the team to each portion of the substructure:

(:Team)-[:COMPETES_IN]->(:League)
(:Team)-[:COMPETES_IN]->(:Substructure1)
(:Team)-[:COMPETES_IN]->(:Substructure2)

and then connect your substructures:

(:League)-[:HAS_SUBSTRUCTURE]->(:Substructure1)-[:HAS_SUBSTRUCTURE]->(:Substructure2)

Then you have a link to your team at each level down into your substructure and can use that depending on how deep you want to go.

I think at this point you could use a query like

MATCH (:League)-[:HAS_SUBSTRUCTURE*0..]->(sub)

where the *0.. says to start at depth level 0 and go out as far as your can following the HAS_SUBSTRUCTURE relationship type.

This what comes to mind initially as I look at what you are trying to do. I am sure there are some other ways of modeling this too.