CODEWITHSUNDEEP
neo4jcypher
kkris1983
kkris1983

Reputation: 493

Neo4j recursive cypher query resulting in nested JSON structure

I am trying to figure out cypher query in order to get nested JSON structure as a result. Below I present an example of the graph.

Graph

MATCH (user:User {name:"User_1"})
OPTIONAL MATCH (user)-[rel*]->(subUser:User)
RETURN *

Query above allows me to get all the nodes and relationships required to transform everything to JSON structure I want but that requires me to process everything after getting the result from querying the database. To achieve that I need to match identity of nodes and relationship in order to get the nested JSON. I was wondering if it is possible to achieve that directly from building cypher query. Important thing is that we do not know how many levels of "child" Users we have starting from User_1

Expected JSON structure:

{ 
    "user": "User_1",
    "children": [
        {
            "user": "User_2",
            "children": [
                {
                    "user": "User_5",
                    "children": []
                }
            ]
        },{
            "user": "User_3",
            "children": [
                {
                    "user": "User_6",
                    "children": []
                }
            ]
        },{
            "user": "User_4",
            "children": []
        }
    ]
}

Is it possible?

Upvotes: 0

Views: 293

Answers (2)

David A Stumpf
David A Stumpf

Reputation: 793

apoc.convert.toTree() is certainly the best answer for the question you asked.

If one is interested in a text output then ORDPATH would be another solution. ORDPATH is a concatenated bitstring which sorts in hierarchical order. More on this at this link. A Neo4j user defined function implementing this is at GitHub.

Upvotes: 0

Charchit Kapoor
Charchit Kapoor

Reputation: 9284

As suggested in the comments by @nimrod serok, you can use the apoc.convert.toTree method, it will give you the tree-structured JSON, as desired, with one caveat, the keys of the JSON will be different. For the data:

MERGE (u1:User{name: 'User1'})
MERGE (u2:User{name: 'User2'})
MERGE (u3:User{name: 'User3'})
MERGE (u4:User{name: 'User4'})
MERGE (u5:User{name: 'User5'})
MERGE (u6:User{name: 'User6'})
MERGE (u1)-[:POINTS]->(u2)-[:POINTS]->(u5)
MERGE (u1)-[:POINTS]->(u3)-[:POINTS]->(u6)
MERGE (u1)-[:POINTS]->(u4)

The query:

MATCH (user:User {name:"User1"})
OPTIONAL MATCH path = (user)-[:POINTS*]->(subUser:User)
WITH collect(path) AS paths
CALL apoc.convert.toTree(paths, true, {nodes: {User: ['name']}})
YIELD value
RETURN value

produces the output:

{
  "_type": "User",
  "name": "User1",
  "_id": 4,
  "points": [
    {
      "_type": "User",
      "name": "User3",
      "_id": 6,
      "points": [
        {
          "_type": "User",
          "name": "User6",
          "_id": 9
        }
      ]
    },
    {
      "_type": "User",
      "name": "User2",
      "_id": 5,
      "points": [
        {
          "_type": "User",
          "name": "User5",
          "_id": 8
        }
      ]
    },
    {
      "_type": "User",
      "name": "User4",
      "_id": 7
    }
  ]
}

as you can see, the relationship type POINTS, comes in place of children, and the key name comes for the user name. The other fields _type and _id can be ignored.

Upvotes: 1

Related Questions