How to join two lists and avoid duplicated properties-CodePudding

I'm working on a query that basically groups results in two different lists that are joined before being returned.

I'm having trouble finding the way to avoid adding elements from the list #2 if there's an element in list #1 with the same specific property (a code).

Example:

List #1:

[ { code:"AA", name: "Anna" }, { code:"BB", name: "Bob" } ]

List #2:

[ { code:"AA", name: null }, { code:"CC", name: "Charles" } ]

Desired output: List:

[ { code:"AA", name: "Anna" }, { code:"BB", name: "Bob" }, { code:"CC", name: "Charles" } ]

Any help is appreciated

Thanks.

EDIT:

I'm using Neo4J so I must use Cypher language.

My query consists of two big MATCHes with its WHEREs clauses. The first part is grouped into a list, then I do the same after matching the second group of results.

CodePudding user response：

You're using INNER JOIN - which means no record returns if it fails to find a match. It is very common, therefore, to return few than all of your rows - especially with so many joins, each having the potential to eliminate some rows.

You should use LEFT JOIN or RIGHT JOIN inappropriate locations, instead, which will return a NULL for unmatched records - but will return all of them. Use ISNULL() if you wish to have a value other than NULL for non-matches.

CodePudding user response：

One option is to add to your query*:

UNWIND listA as item
WITH collect(item) as listA, collect(item.code) as keys, listB
UNWIND listB as item
WITH item, listA, keys
WHERE NOT item.code in keys
WITH collect(item) as listB, listA
RETURN listB listA

This will store the keys of listA as keys and will keep on listB only items that their keys are not in that list.

When I used a sample data:

MERGE (b:DATA_A { code:"BB", name: "Bob" })
MERGE (c:DATA_B { code:"CC", name: "Charles" })
MERGE (d:DATA_A { code:"AA", name: "Anna" })
MERGE (a:DATA_B { code:"AA", name: "null" })
MERGE (e:DATA_B { code:"DD", name: "Dan" })
MERGE (f:DATA_A { code:"EE", name: "El" })

With this query:

MATCH (n:DATA_A)
WITH collect({name: n.name, code: n.code}) as listA
MATCH (n:DATA_B)
WITH collect({name: n.name, code: n.code}) as listB, listA

UNWIND listA as item
WITH collect(item) as listA, collect(item.code) as keys, listB
UNWIND listB as item
WITH item, listA, keys
WHERE NOT item.code in keys
WITH collect(item) as listB, listA
RETURN listB listA

I got the expected 5 results (with { code:"AA", name: "Anna" } and without { code:"AA", name: "null" }).

*First two lines may be probably done when you already collect the current list.