Neo4j return nested JSON

Question

My Neo4j database contains family tree relationships.

I would like to extract that data in a nested JSON format like so:

{  
"firstname":"Jon",
"lastname":"Smith",
"parents":[  
  {  
     "firstname":"Anna",
     "lastname":"Smith",
     "parents":[  
        {  
           "furstname":"Peter",
           "lastname":"Doe",
           "parents":[  
              {  
                 "firstname":"Jessica",
                 "lastname":"Doe"
              },
              {  
                 "firstname":"Clayton",
                 "lastname":"Doe"
              }
           ]
        },
        {  
           "firstname":"Nell",
           "lastname":"Gordon",
           "parents":[  
              {  
                 "firstname":"Jessica",
                 "lastname":"Roberts"
              },
              {  
                 "firstname":"Randy",
                 "lastname":"Roberts"
              }
           ]
        }
     ]
  }
]
}

in order to visualize it.

I have tried the following query:

MATCH path = (p:Person)-[r:PARENT_OF*1..3]-(k:Person) 
WHERE k.id = '1887' 
UNWIND r as rel 
RETURN StartNode(rel).firstname, rels(path), EndNode(rel).firstname

with the py2neo library like so:

dumps(graph.run(query).data())

but the JSON was not nested like I desired.

Is there a query that would help me achieve this or I should do the nesting in other programming language?

https://neo4j.com/blog/cypher-graphql-neo4j-3-1-preview/ this might be helpful to get a feeling how you can return nested JSONs — Tomaž Bratanič, Jul 05 '17 at 10:18
Check https://stackoverflow.com/questions/38578114/neo4j-cypher-hierarchical-tree-build-response-to-json ... apoc has a procedure for it. — Tom Geudens, Jul 05 '17 at 10:22

score 15 · Accepted Answer · answered Jul 05 '17 at 12:00

As sayd in the comments, you can use the APOC Procedure apoc.convert.toTree. Look:

1 - Creating a sample data set based on your question:

CREATE (jonsmith:Person {firstname:"Jon", lastname:"Smith"})
CREATE (annasmith:Person {firstname:"Anna", lastname:"Smith"})
CREATE (peterdoe:Person {firstname:"Peter", lastname:"Doe"})
CREATE (jessicadoe:Person {firstname:"Jessica", lastname:"Doe"})
CREATE (claytondoe:Person {firstname:"Clayton", lastname:"Doe"})
CREATE (nellgordon:Person {firstname:"Nell", lastname:"Gordon"})
CREATE (jessicaroberts:Person {firstname:"Jessica", lastname:"Roberts"})
CREATE (randyroberts:Person {firstname:"Randy", lastname:"Roberts"})

CREATE (jonsmith)-[:PARENT_OF]->(annasmith)
CREATE (annasmith)-[:PARENT_OF]->(peterdoe)
CREATE (annasmith)-[:PARENT_OF]->(nellgordon)
CREATE (peterdoe)-[:PARENT_OF]->(jessicadoe)
CREATE (peterdoe)-[:PARENT_OF]->(claytondoe)
CREATE (nellgordon)-[:PARENT_OF]->(jessicaroberts)
CREATE (nellgordon)-[:PARENT_OF]->(randyroberts)

2 - Running the query:

MATCH path = (jon:Person {firstname:'Jon', lastname:'Smith'})-[:PARENT_OF*]-(:Person)
WITH collect(path) as paths
CALL apoc.convert.toTree(paths) yield value
RETURN value;

3 - As result:

{
  "_type": "Person",
  "_id": 9,
  "firstname": "Jon",
  "lastname": "Smith",
  "parent_of": [
    {
      "_id": 10,
      "_type": "Person",
      "firstname": "Anna",
      "lastname": "Smith",
      "parent_of": [
        {
          "_id": 11,
          "_type": "Person",
          "firstname": "Peter",
          "lastname": "Doe",
          "parent_of": [
            {
              "_id": 12,
              "_type": "Person",
              "firstname": "Jessica",
              "lastname": "Doe"
            },
            {
              "_id": 13,
              "_type": "Person",
              "firstname": "Clayton",
              "lastname": "Doe"
            }
          ]
        },
        {
          "_id": 14,
          "_type": "Person",
          "firstname": "Nell",
          "lastname": "Gordon",
          "parent_of": [
            {
              "_id": 15,
              "_type": "Person",
              "firstname": "Jessica",
              "lastname": "Roberts"
            },
            {
              "_id": 16,
              "_type": "Person",
              "firstname": "Randy",
              "lastname": "Roberts"
            }
          ]
        }
      ]
    }
  ]
}

Remember to install APOC procedures according the version of Neo4j are you using. Take a look in the version compatibility matrix.

I have been fiddling with `apoc.convert.toTree`. The example that you provided works fine but when I try it with my data like this: `MATCH p=(n:Person)-[r:PARENT_OF*]-(m:Person) WHERE n.id='1887' WITH COLLECT(p) AS ps CALL apoc.convert.toTree(ps) yield value RETURN value;` the query runs forever. If I direct the graph like so : `MATCH p=(n:Person)-[r:PARENT_OF*]->(m:Person)` then I get only the children of person with `id:1887` and his children's children but I don't get that person's parents. I would need a way to nest all the `PARENT_OF` nodes. — Porjaz, Jul 05 '17 at 12:17
@Porjaz Your first query runs forever because you are traveling the graph in all directions and any depth starting from a node. That is: a very expensive operation over the graph. What about try limiting the depth (something like `[:PARENT_OF*1..3]`)? — Bruno Peres, Jul 05 '17 at 12:36
It works only with `[:PARENT_OF*1..2]` but this way I don't get all the relationships. I will probably write a custom script that will nest the relationships correctly — Porjaz, Jul 05 '17 at 13:28

Neo4j return nested JSON

1 Answers1