Neo4j Condition based output

[rpatel78648]

In Neo4j, I have relation (p:Person)-[:HAS_DOCUMNET]->(d:Document) and Required only 1 row data per person (personId and documentType) I required to develop query output on the basis of below priority condition
1)If documentType="Passport" is present then should come with personId.(1st Priority) 2)If documentType="VoterCard" is present then should come with personeId.(If Passport is not present)
3)If documentType="PanCard" is present then should come withe personId.(If VoterId is not present)

Data Present like below:

(P1:Person)-[:HAS_Document]->("Passpot","VoterCard",PanCard)`
(P2:Person)-[:HAS_Document]->("VoterCard","PanCard")`
(P3:Person)-[:HAS_Document]->("PanCard","AadharCard","VoterCard")
(P4:Person)-[:HAS_Document]->("PanCard")

Output should be like (Only one document required against one person based on priority😞

PName    Doc.Type
-------  -----------
  P1      "Passport"
  P2      "VoterCard"
  P3      "VoterCard"
  P4      "PanCard"

I tried below query which is not working

I am trying below query which is not working:

Match(p:Person)-[:HAS_DOCUMNET]->(d1:Document{Doc_Type:"Passport"})
Optional Match(p:Person)-[:HAS_DOCUMNET]->(d2:Document{Doc_Type:"VoterCard"})
OptionalMatch(p:Person)-[:HAS_DOCUMNET]->(d3:Document{Doc_Type:"PanCard"})
Return p.PName as PName,coalesce(d1.Doc_Type,coalesce(d2.Doc_Type,coalesce(d3.Doc_Type))) as Doc.Type 

[ameyasoft]

Try this:

Match(p:Person)-[:HAS_DOCUMNET]->(d1:Document)

with p.PName as PName, d1.Doc_Type as doctype
with PName, doctype,

CASE

CASE
   WHEN doctype = "Passport" THEN 'Passport'
   WHEN doctype = "VoterCard" THEN 'VoterCard'
   WHEN doctype = "PanCard" THEN 'PanCard'
   ELSE doctype END as dtype
   
   return PName, dtype

[rpatel78648]

Hey ameysoft,
Thank you for your reply, with your query i am getting all documentType with respect to member.
I found answer and it is only work if you add collect() in with clause otherwise you will get all docType records.
here i am updating my question and putting one more relation and make it complex can you please check updated question and help me with that.

[rpatel78648]

Question: In Neo4j, I have relation (p:Person)-[:HAS_DOCUMNET]->(id:Identification)-[:HAS_DESCRIPTION]->(d:Document) and Required only 1 row data per person (personId,documentType and idNumber) I required to develop query output on the basis of below priority condition 1)If documentType="Passport" is present then should come with personId and respected idNumber.(1st Priority) 2)If documentType="VoterCard" is present then should come with personeId and respected idNumber.(If Passport is not present) 3)If documentType="PanCard" is present then should come withe personId and respected idNumber.(If VoterId is not present)

Data Present like below:

(P1:Person)-[:HAS_Document]->('id1','id2','id3')->[:HAS_DESCRIPTION]->("Passpot","VoterCard",PanCard)`
(P2:Person)-[:HAS_Document]->('id4','id5')->[:HAS_DESCRIPTION]->("VoterCard","PanCard")`
(P3:Person)-[:HAS_Document]->('id6','id7','id8')->[:HAS_DESCRIPTION]->("PanCard","AadharCard","VoterCard")
(P4:Person)-[:HAS_Document]->('id9')->[:HAS_DESCRIPTION]->("PanCard")

Output should be like :

PName    Doc.Type     IDNumber
-------  -----------  ---------
  P1      "Passport"    id1
  P2      "VoterCard"   id4
  P3      "VoterCard"   id8
  P4      "PanCard"     id9

[ameyasoft]

Try this:

MATCH p:Person)-[:HAS_DOCUMNET]->(id:Identification)-[:HAS_DESCRIPTION]->(d:Document)

with p.PName as PName, id.id as IDNumber, d1.Doc_Type as doctype
with PName, doctype,

CASE

CASE
   WHEN doctype = "Passport" THEN 'Passport'
   WHEN doctype = "VoterCard" THEN 'VoterCard'
   WHEN doctype = "PanCard" THEN 'PanCard'
   ELSE doctype END as dtype
   
RETURN PName, dtype, IDNumber

[rpatel78648]

hey ameyasoft,

By the suggestion you have provided, I am getting all records but I required only one documnet id and number record per Person on the basis of priority mentioned in question.

[ameyasoft]

ameyasoft:

MATCH p:Person)-[:HAS_DOCUMNET]->(id:Identification)

What's the output from this statement:

MATCH (p:Person)-[:HAS_DOCUMNET]->(id:Identification)

Post a sample results.

[andrew_bowman]

You can do this with CASE, to provide a value for the given document type, and ordering and limiting so you only get one result.

Also, your example data and queries are using mismatched case and spelling for relationship types and property values, which makes it a little tough to provide a correct query. For now I will use :HAS_DOCUMNET and HAS_DESCRIPTION, and you can adjust on your end for whatever you actually have in your db. Likewise, adjust properties used to match what you have in your db.

For a query regarding just an individual:

WITH {Passport:1, VoterCard:2, PanCard:3} as typeValueMap
MATCH (p:Person)-[:HAS_DOCUMNET]->(id:Identification)-[:HAS_DESCRIPTION]->(d:Document)
WHERE p.PName = $name
WITH p, id, d, typeValueMap[d.Type] as value
ORDER BY value ASC
LIMIT 1
RETURN p.PName as PName, d.Type as DocType, id.idNumber as IDNumber

If this is meant to apply to all persons, and not just for a specific person, then we can use a subquery (you'll need at least Neo4j 4.1) so the LIMIT will apply per person and not for the entire query:

WITH {Passport:1, VoterCard:2, PanCard:3} as typeValueMap
MATCH (p:Person)
CALL {
  WITH typeValueMap, p
  MATCH (p)-[:HAS_DOCUMNET]->(id:Identification)-[:HAS_DESCRIPTION]->(d:Document)
  WITH p, id, d, typeValueMap[d.Type] as value
  ORDER BY value ASC
  LIMIT 1
  RETURN p.PName as PName, d.Type as DocType, id.idNumber as IDNumber
}
RETURN PName, DocType, IDNumber

[clem]

Hmmm.... I wonder if you want to rethink your model (I didn't quite follow what you're trying to do....)

It seems to me that a person can have one or more documents. (Of course, it would be weird if one document mapped back to more than one person.)

It seems like it would be more natural to have:

(p:Person {Name:"Albert"}) -[:HAS]->(pass:Passport {IDNumber:"xxx"})
(p) -[:HAS]->(vote:VoterCard {IDNumber:"yyy"})
(p) -[:HAS]->(pan:PanCard {IDNumber:"zzz"})

It also possible to give a node two or more labels, so you could have instead:

(p:Person {Name:"Albert"}) -[:HAS]->(pass:Document:Passport {IDNumber:"xxx"})
(p) -[:HAS]->(vote:Document:VoterCard {IDNumber:"yyy"})
(p) -[:HAS]->(pad:Document:PanCard {IDNumber:"zzz"})

I think you can then do existence conditionals for:

(p:Person)-[:HAS]->(d:Passport)

and then doing what you want.

Maybe you can also create property is a LIST of Documents in the order you want (somehow).

IMHO, your model is kind of clumsy and will make some queries unnecessarily complicated.