How to concatenate three columns into one and obtain count of unique entries among them using Cypher neo4j?

Question

I can query using Cypher in Neo4j from the Panama database the countries of three types of identity holders (I define that term) namely Entities (companies), officers (shareholders) and Intermediaries (middle companies) as three attributes/columns. Each column has single or double entries separated by colon (eg: British Virgin Islands;Russia). We want to concatenate the countries in these columns into a unique set of countries and hence obtain the count of the number of countries as new attribute.

For this, I tried the following code from my understanding of Cypher:

MATCH (BEZ2:Officer)-[:SHAREHOLDER_OF]->(BEZ1:Entity),(BEZ3:Intermediary)-[:INTERMEDIARY_OF]->(BEZ1:Entity)
WHERE BEZ1.address CONTAINS "Belize" AND 
  NOT ((BEZ1.countries="Belize" AND  BEZ2.countries="Belize" AND BEZ3.countries="Belize") OR
    (BEZ1.status IN ["Inactivated", "Dissolved shelf company", "Dissolved", "Discontinued", "Struck / Defunct / Deregistered", "Dead"]))
SET BEZ4.countries= (BEZ1.countries+","+BEZ2.countries+","+BEZ3.countries) 
RETURN BEZ3.countries AS IntermediaryCountries, BEZ3.name AS 
  Intermediaryname, BEZ2.countries AS OfficerCountries , BEZ2.name AS 
  Officername, BEZ1.countries as EntityCountries, BEZ1.name AS Companyname, 
  BEZ1.address AS CompanyAddress,DISTINCT count(BEZ4.countries) AS NoofConnections

The relevant part is the SET statement in the 7th line and the DISTINCT count in the last line. The code shows error which makes no sense to me: Invalid input 'u': expected 'n/N'. I guess it means to use COLLECT probably but we tried that as well and it shows the error vice-versa'd between 'u' and 'n'. Please help us obtain the output that we want, it makes our job hell lot easy. Thanks in advance!

EDIT: Considering I didn't define variable as suggested by @Cybersam, I tried the command CREATE as following but it shows the error "Invalid input 'R':" for the command RETURN. This is unfathomable for me. Help really needed, thank you.

CODE 2:

MATCH (BEZ2:Officer)-[:SHAREHOLDER_OF]->(BEZ1:Entity),(BEZ3:Intermediary)-
[:INTERMEDIARY_OF]->(BEZ1:Entity)
WHERE BEZ1.address CONTAINS "Belize" AND 
NOT ((BEZ1.countries="Belize" AND  BEZ2.countries="Belize" AND 
BEZ3.countries="Belize") OR
(BEZ1.status IN ["Inactivated", "Dissolved shelf company", "Dissolved", 
"Discontinued", "Struck / Defunct / Deregistered", "Dead"]))
CREATE (p:Connections{countries: 
split((BEZ1.countries+";"+BEZ2.countries+";"+BEZ3.countries),";")
RETURN BEZ3.countries AS IntermediaryCountries, BEZ3.name AS 
Intermediaryname, BEZ2.countries AS OfficerCountries , BEZ2.name AS 
Officername, BEZ1.countries as EntityCountries, BEZ1.name AS Companyname, 
BEZ1.address AS CompanyAddress,  AS TOTAL, collect (DISTINCT 
COUNT(p.countries)) AS NumberofConnections

Lines 8 and 9 are the ones new and to be in examination.

cybersam cybersam · Accepted Answer · 2017-04-21T18:47:18

First Query

You never defined the identifier BEZ4, so you cannot set a property on it.

Second Query (which should have been posted in a separate question):

You have several typos and a syntax error.

This query should not get an error (but you will have to determine if it does what you want):

MATCH (BEZ2:Officer)-[:SHAREHOLDER_OF]->(BEZ1:Entity),(BEZ3:Intermediary)- [:INTERMEDIARY_OF]->(BEZ1:Entity)
WHERE BEZ1.address CONTAINS "Belize" AND  NOT ((BEZ1.countries="Belize" AND  BEZ2.countries="Belize" AND  BEZ3.countries="Belize") OR (BEZ1.status IN ["Inactivated", "Dissolved shelf company", "Dissolved",  "Discontinued", "Struck / Defunct / Deregistered", "Dead"]))
CREATE (p:Connections {countries: split((BEZ1.countries+";"+BEZ2.countries+";"+BEZ3.countries), ";")})
RETURN BEZ3.countries AS IntermediaryCountries,
  BEZ3.name AS  Intermediaryname,
  BEZ2.countries AS OfficerCountries ,
  BEZ2.name AS  Officername,
  BEZ1.countries as EntityCountries,
  BEZ1.name AS Companyname,
  BEZ1.address AS CompanyAddress,
  SIZE(p.countries) AS NumberofConnections;

Problems with the original:

The CREATE clause was missing a closing } and also a closing ).
The RETURN clause had a dangling AS TOTAL term.
collect (DISTINCT COUNT(p.countries)) was attempting to perform nested aggregation, which is not supported. In any case, even if it had worked, it probably would not have returned what you wanted. I suspect that you actually wanted the size of the p.countries collection, so that is what I used in my query.

How to concatenate three columns into one and obtain count of unique entries among them using Cypher neo4j?

1 Answers

First Query

Second Query (which should have been posted in a separate question):