cancel
Showing results for 
Search instead for 
Did you mean: 

Keys(n) sorting problems with DISTINCT

rob2
Node Clone

HI

when i execute the folllowing cmd

`MATCH (n:process) RETURN DISTINCT keys(n) AS ProcessKey;`

I get 2 rows back, keys ar identical but sorting is different

["timestamp_unix_changed", "timestamp_unix_lastread", "name", "timestamp_unix_creation", "guid"]
["timestamp_unix_creation", "timestamp_unix_changed", "name", "timestamp_unix_lastread", "guid"]
  1. why - is the order at creation responsible?
  2. since the keys are identical how can I sort the Keys?

Thanks rob

You can reproduce the error with


MATCH (n:test) Delete n;
CREATE (n:test {name: 'I', prob_AA: 'terst'});
CREATE (n:test {prob_AA: '12QW', name: 'II'});
CREATE (n:test {name: 'III'});
MATCH (n:test {name: 'III'}) SET n.prob_AA = "SDFGHGFDSDFGHJHGFDSASDFGHJK";
MATCH (n:test) RETURN DISTINCT keys(n) AS Keys;
["prob_AA", "name"] 
["name", "prob_AA"]
1 ACCEPTED SOLUTION

clem
Graph Steward

Oddly enough, I recently discovered a similar problem that's not correctly answered in this otherwise very interesting and useful video:
https://www.youtube.com/watch?v=BN5T8IimB78 at 31:40

Here's my solution to fix it

MATCH(p1:Person)-[:ACTED_IN]->(m:Movie)<-[:ACTED_IN]-(p2:Person)
WHERE m.title = 'Top Gun'
WITH p1, p2 ORDER BY [p1.name]
RETURN COLLECT(distinct (apoc.coll.sort([p1.name, p2.name])))

View solution in original post

4 REPLIES 4

Cobra
Ninja
Ninja

Hello @rob2

I'm not sure what you were trying to achieve, but this is a query (you will need APOC) that will collect all keys and sort them and it will return a set of keys:

MATCH (n:test) RETURN apoc.coll.sort(apoc.coll.toSet(apoc.coll.flatten(collect(keys(n))))) AS Keys;

Regards,
Cobra

HI
i should have told you what I try to archiev. It shell be a consitency check. All nodes withe the same label MUST have the same keys. I try to finde out if this is true.
Unfortunately, your answer is not a solution even if I have modified it a little ... (my fault)

MATCH (n:test) Delete n;
CREATE (n:test {name: 'I', prob_AA: 'terst'});
CREATE (n:test {prob_AA: '12QW', name: 'II'});
CREATE (n:test {name: 'III'});
MATCH (n:test {name: 'III'}) SET n.prob_AA = "SDFGHGFDSDFGHJHGFDSASDFGHJK";
CREATE (n:test {prob_cc: '12QW', name: 'OTHER'});
MATCH (n) RETURN apoc.coll.sort(apoc.coll.toSet(apoc.coll.flatten(collect(keys(n))))) AS Keys, labels(n);

Any idea how I can get the info? Create would be if i get a 0 if it is TRUE and FAlse if the keys are different.
thansk rob

The properties of a node, by design, do not follow any order. That's why

CREATE (n:test {name: 'I', prob_AA: 'terst'});
CREATE (n:test {prob_AA: '12QW', name: 'II'});

Here you used 'CREATE' and this creates two nodes. Same query if run with MERGE:

MERGE (n:test {name: 'I', prob_AA: 'terst'});
MERGE (n:test {prob_AA: '12QW', name: 'II'});

This will create only one node and displays the order of the properties as in the first created node. MERGE is CREATE IF NOT EXISTS.

clem
Graph Steward

Oddly enough, I recently discovered a similar problem that's not correctly answered in this otherwise very interesting and useful video:
https://www.youtube.com/watch?v=BN5T8IimB78 at 31:40

Here's my solution to fix it

MATCH(p1:Person)-[:ACTED_IN]->(m:Movie)<-[:ACTED_IN]-(p2:Person)
WHERE m.title = 'Top Gun'
WITH p1, p2 ORDER BY [p1.name]
RETURN COLLECT(distinct (apoc.coll.sort([p1.name, p2.name])))