how to use Neo4J driver to query nodes and related nodes and adding properties to the returned nodes

Question

I am trying to retrieve nodes and related nodes from a Neo4J dbase where i also want to dynamically add properties to the returned nodes. I have the following model: a Country contains Cities, i have the following sample data:

CREATE (n:Country { identifier: '20f95f76-be40-4cff-98c8-8d045b0552eb', revision: '1', countryCode: 'BE', name: 'Belgium', type:'Country' });
CREATE (n:Country { identifier: '4c78a5b2-7fe6-4b8c-bd35-f2a0aa0ec160', revision: '1', countryCode: 'NL', name: 'Netherlands', type:'Country' });
CREATE (n:Country { identifier: 'aa265519-4765-477d-99f9-c65bf0c202d8', revision: '1', countryCode: 'DE', name: 'Germany', type:'Country' });

CREATE (n:City { identifier: '8147f29c-39f2-4123-9b73-298de2b871f9', revision: '1', name: 'Antwerp', type:'City' })
CREATE (n:City { identifier: '8058bfc4-9fd3-4df7-8035-0adb53da713c', revision: '1', name: 'Brussels', type:'City' })

MATCH (country:Country), (city:City)
WHERE country.identifier = '20f95f76-be40-4cff-98c8-8d045b0552eb' AND city.identifier = '8147f29c-39f2-4123-9b73-298de2b871f9'
CREATE (country)-[:CONTAINS]->(city)

MATCH (country:Country), (city:City)
WHERE country.identifier = '20f95f76-be40-4cff-98c8-8d045b0552eb' AND city.identifier = '8058bfc4-9fd3-4df7-8035-0adb53da713c'
CREATE (country)-[:CONTAINS]->(city)

What I would like to get out of the database is the Country nodes as well as the City nodes, but i need to add a "city" property to the Country node that is returned which contains an array of the identifiers of the contained city. And instead of storing the _type property, i would like to add it dynamically based on the label of the node.

Currently i am using the following query:

MATCH (country:Country)
OPTIONAL MATCH (country)-[:CONTAINS]-(city:City)
RETURN country {.*, city: collect( distinct city.identifier )}, city {.*, container: country.identifier}

I get the following results:

[
  {
    "country": {
      "city": [
        "8058bfc4-9fd3-4df7-8035-0adb53da713c"
      ],
      "name": "Belgium",
      "identifier": "20f95f76-be40-4cff-98c8-8d045b0552eb",
      "revision": "1",
      "countryCode": "BE",
      "type": "Country"
    },
    "city": {
      "container": "20f95f76-be40-4cff-98c8-8d045b0552eb",
      "identifier": "8058bfc4-9fd3-4df7-8035-0adb53da713c",
      "name": "Brussels",
      "revision": "1",
      "type": "City"
    }
  },
  {
    "country": {
      "city": [
        "8147f29c-39f2-4123-9b73-298de2b871f9"
      ],
      "name": "Belgium",
      "identifier": "20f95f76-be40-4cff-98c8-8d045b0552eb",
      "revision": "1",
      "countryCode": "BE",
      "type": "Country"
    },
    "city": {
      "container": "20f95f76-be40-4cff-98c8-8d045b0552eb",
      "identifier": "8147f29c-39f2-4123-9b73-298de2b871f9",
      "name": "Antwerp",
      "revision": "1",
      "type": "City"
    }
  },
  {
    "country": {
      "city": [],
      "name": "Netherlands",
      "identifier": "4c78a5b2-7fe6-4b8c-bd35-f2a0aa0ec160",
      "revision": "1",
      "countryCode": "NL",
      "type": "Country"
    },
    "city": null
  },
  {
    "country": {
      "city": [],
      "name": "Germany",
      "identifier": "aa265519-4765-477d-99f9-c65bf0c202d8",
      "revision": "1",
      "countryCode": "DE",
      "type": "Country"
    },
    "city": null
  }
]

As you can see, the Belgium node is returned twice (i was expecting it to be returned only once), each time there is a match on contained City. I do want to return the Country nodes even if they don't contain cities.

Some help would be appreciated

cybersam cybersam · Accepted Answer · 2020-01-08T20:58:38

This query might be suitable:

MATCH (country:Country)
OPTIONAL MATCH (country)-[:CONTAINS]-(city:City)
RETURN country, COLLECT(city) AS cities

The result is:

╒══════════════════════════════════════════════════════════════════════╤══════════════════════════════════════════════════════════════════════╕
│"country"                                                             │"cities"                                                              │
╞══════════════════════════════════════════════════════════════════════╪══════════════════════════════════════════════════════════════════════╡
│{"name":"Belgium","identifier":"20f95f76-be40-4cff-98c8-8d045b0552eb",│[{"name":"Antwerp","identifier":"8147f29c-39f2-4123-9b73-298de2b871f9"│
│"type":"Country","countryCode":"BE","revision":"1"}                   │,"type":"City","revision":"1"},{"name":"Brussels","identifier":"8058bf│
│                                                                      │c4-9fd3-4df7-8035-0adb53da713c","type":"City","revision":"1"}]        │
├──────────────────────────────────────────────────────────────────────┼──────────────────────────────────────────────────────────────────────┤
│{"name":"Netherlands","identifier":"4c78a5b2-7fe6-4b8c-bd35-f2a0aa0ec1│[]                                                                    │
│60","type":"Country","countryCode":"NL","revision":"1"}               │                                                                      │
├──────────────────────────────────────────────────────────────────────┼──────────────────────────────────────────────────────────────────────┤
│{"name":"Germany","identifier":"aa265519-4765-477d-99f9-c65bf0c202d8",│[]                                                                    │
│"type":"Country","countryCode":"DE","revision":"1"}                   │                                                                      │
└──────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────────────────┘

how to use Neo4J driver to query nodes and related nodes and adding properties to the returned nodes

1 Answers