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?

Answer 1

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.