title: How to select distinct graph nodes by property
body: <p>I have a database including PlayStation games and it contains games from all regions and platforms. Some of the games from different regions have the same title and platform so I would like to filter out "duplicates". At this time, I don't have region information on each game so the best I can do is filter out by game name and platform.</p>
  
  <p>Is it possible to select distinct nodes by property? I seem to remember that you can return distinct rows based on a column in SQL, but it seems that Cypher applies distinct to the entire row and not just a specific column.</p>
  
  <p>I would like to achieve something like the following:</p>
  
  <pre><code>MATCH (game:PSNGame) RETURN game WHERE distinct game.TitleName, distinct game.Platforms
  </code></pre>
  
  <p>The above query if it were valid would return all PSNGame nodes with a distinct TitleName and Platforms combination. Since the above query is not valid Cypher, I have tried returning a list of distinct TitleName/Platforms where distinct is applied to both columns.</p>
  
  <p>The query I have for returning the distinct TitleName/Platforms list looks like this:</p>
  
  <pre><code>MATCH (game:PSNGame) RETURN distinct game.TitleName, game.Platforms
  </code></pre>
  
  <p>The JSON response from Neo4j is similar to this:</p>
  
  <pre><code>[["God of War", ["PS3", "PSVITA"]], ["God of War II", ["PS3", "PSVITA"]]]
  </code></pre>
  
  <p>The problem I'm facing is that the JSON response is not really an object with properties. It's more of an array of arrays. If I could get the response to be more like an object, I could deserialize without issues. I tried to deserialize as an <code>IList&lt;PsnGame&gt;</code>, but haven't had much luck.</p>
  
  <p>Here's my POCO for the <code>IList&lt;PsnGame&gt;</code> implementation:</p>
  
  <pre><code>public class PsnGame
  {
      public string TitleName { get; set; }
      public string[] Platforms { get; set; }
  }
  </code></pre>
  
  <p>EDIT:
  Here is the simplest example of my Neo4jClient query:</p>
  
  <pre><code>// helper function for handling searching by name and platform
  private ICypherFluentQuery BuildPSNGamesQuery(string gameName, string platform)
  {
      var query = client.Cypher
          .Match("(g:PSNGame)");
  
      if (!string.IsNullOrWhiteSpace(gameName))
      {
          query = query.Where($"g.TitleName =~ \"(?i).*{gameName}.*\"");
          if (!string.IsNullOrWhiteSpace(platform) &amp;&amp; platform.ToLower() != "all")
          {
              query = query.AndWhere($"\"{platform}\" in g.Platforms");
          }
      }
      else
      {
          if (!string.IsNullOrWhiteSpace(platform) &amp;&amp; platform.ToLower() != "all")
          {
              query = query.Where($"\"{platform}\" in g.Platforms");
          }
      }
  
      return query;
  }
  </code></pre>
  
  <p>Distinct games:</p>
  
  <pre><code>var distinctGames = await BuildPSNGamesQuery(gameName, platform)
          .With("DISTINCT g.TitleName AS TitleName, g.Platforms AS Platforms")
          .With("{ TitleName: TitleName, Platforms: Platforms } as Games")
          .OrderBy("TitleName")
          .Return&lt;PsnGame&gt;("Games")               
          .Skip((pageNumber - 1) * pageSize)
          .Limit(pageSize)
          .ResultsAsync;
  </code></pre>
  
  <p>All games (somehow need to filter based on previous query):</p>
  
  <pre><code>var results = await BuildPSNGamesQuery(gameName, platform)
          .Return(g =&gt; new Models.PSN.Composite.PsnGame
          {
              Game = g.As&lt;PsnGame&gt;()
          })
          .OrderBy("g.TitleName")
          .Skip((pageNumber - 1) * pageSize)
          .Limit(pageSize)
          .ResultsAsync;
  </code></pre>
  
  <p>By using a map, I'm able to return the TitleName/Platforms pairing that I want, but I suspect I'll need to do a <code>collect</code> on the Platforms to get all platforms for a particular game title. Then I can filter the entire games list by the distinctGames that I return. However, I would prefer to perform a request and merge the queries to reduce HTTP traffic.</p>
  
  <p>An example of duplicates can be seen on my website here:
  <a href="https://www.gamerfootprint.com/#/games/ps" rel="nofollow">https://www.gamerfootprint.com/#/games/ps</a></p>
  
  <p>Also, the data for duplicates looks something like this:</p>
  
  <pre><code>MATCH (n:PSNGame) 
  WHERE n.TitleName = '1001 Spikes' 
  RETURN n.TitleName, n.Platforms LIMIT 25
  </code></pre>
  
  <p>JSON:</p>
  
  <pre><code>{
     "columns":[
        "n.TitleName",
        "n.Platforms"
     ],
     "data":[
        {
           "row":[
              "1001 Spikes",
              [
                 "PSVITA"
              ]
           ],
           "graph":{
              "nodes":[
  
              ],
              "relationships":[
  
              ]
           }
        },
        {
           "row":[
              "1001 Spikes",
              [
                 "PS4"
              ]
           ],
           "graph":{
              "nodes":[
  
              ],
              "relationships":[
  
              ]
           }
        }
     ],
     "stats":{
        "contains_updates":false,
        "nodes_created":0,
        "nodes_deleted":0,
        "properties_set":0,
        "relationships_created":0,
        "relationship_deleted":0,
        "labels_added":0,
        "labels_removed":0,
        "indexes_added":0,
        "indexes_removed":0,
        "constraints_added":0,
        "constraints_removed":0
     }
  }
  </code></pre>
  
  <p>EDIT: 10-31-15<br>
  I was able to get distinct game title and platforms returning with the platforms for each game rolled up into a single collection. My new query is the following:</p>
  
  <pre><code>MATCH (game:PSNGame)
  WITH DISTINCT game.TitleName as TitleName, 
         game.Platforms as coll UNWIND coll as Platforms
  WITH TitleName as TitleName, COLLECT(DISTINCT Platforms) as Platforms
  RETURN TitleName, Platforms
  ORDER BY TitleName
  </code></pre>
  
  <p>Here is a small subset of the results:</p>
  
  <pre><code>{
     "columns":[
        "TitleName",
        "Platforms"
     ],
     "data":[
        {
           "row":[
              "1001 Spikes",
              [
                 "PSVITA",
                 "PS4"
              ]
           ],
           "graph":{
              "nodes":[
  
              ],
              "relationships":[
  
              ]
           }
        }
     ],
     "stats":{
        "contains_updates":false,
        "nodes_created":0,
        "nodes_deleted":0,
        "properties_set":0,
        "relationships_created":0,
        "relationship_deleted":0,
        "labels_added":0,
        "labels_removed":0,
        "indexes_added":0,
        "indexes_removed":0,
        "constraints_added":0,
        "constraints_removed":0
     }
  }
  </code></pre>
  
  <p>Finally, 1001 Spikes is in the list once and has both PS VITA and PS4 listed as platforms. Now, I need to figure out how to grab the full game nodes and filter against the above query. </p>