Neo4j returns nested JSON
My Neo4j database contains family tree relationships.
I would like to extract this data in a nested JSON format like this:
{
"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"
}
]
}
]
}
]
}
to visualize it.
I've 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 py2neo library:
dumps(graph.run(query).data())
but the JSON was not nested as I wanted.
Is there a query that can help me achieve this, or should I nest in another programming language?
+3
source to share
1 answer
As said in the comments, you can use the APOC Procedure apoc.convert.toTree
. Take a look:
1 - Create a sample dataset 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 a request:
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 a 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 set APOC procedures according to the Neo4j version you are using. See version compatibility .
+2
source to share