CODEWITHSUNDEEP
azure-cosmosdbgremlintinkerpop3graph-traversal
dotsa
dotsa

Reputation: 941

Group Parent Vertex and Children through Edges into master/sub json array

First of i am using azure cosmos db.

A person works_for multiple Offices. Each Office can be IsMaster or not. If it is a IsMaster it can have master_of edge to another Office. Each works_for edge has AccessLevel property for a person.

Logic: Given a Person name, get all Offices that person works_for that are IsMaster = 'true'. Then return all IsMaster with AccessLevel taken from works_for and corresponding SubOffices that each IsMaster has through master_of edge and corresponding AccessLevel through works_for

My end goal is to produce a JSON object below given a person name:

[
  {
    "Master": {
      "Name": "Seattle",
      "AccessLevel": "Admin"
    },
    "Subs": [
      {
        "Name": "Portland",
        "AccessLevel": "NonAdmin"
      },
      {
        "Name": "Vancouver",
        "AccessLevel": "Admin"
      }
    ]
  },
  {
    "Master": {
      "Name": "New York",
      "AccessLevel": "NonAdmin"
    },
    "Subs": [
      {
        "Name": "Boston",
        "AccessLevel": "NonAdmin"
      },
      {
        "Name": "Orlando",
        "AccessLevel": "Admin"
      }
    ]
  }
]

enter image description here

g.addV('Office').property('Name','Seattle').property('IsMaster','true').as('ow1')
.addV('Office').property('Name','Portland').property('IsMaster','false').as('ow2')
.addV('Office').property('Name','Vancouver').property('IsMaster','false').as('ow3')
.addV('Office').property('Name','New York').property('IsMaster','true').as('oe1')
.addV('Office').property('Name','Boston').property('IsMaster','false').as('oe2')
.addV('Office').property('Name','Orlando').property('IsMaster','false').as('oe3')
.addV('Person').property('Name','Zodiak').as('p')
.select('p').addE('works_for').property('AccessLevel','Admin').to(g.V().hasLabel('Office').has('Name', 'Seattle'))
.select('p').addE('works_for').property('AccessLevel','NonAdmin').to(g.V().hasLabel('Office').has('Name', 'Portland'))
.select('p').addE('works_for').property('AccessLevel','Admin').to(g.V().hasLabel('Office').has('Name', 'Vancouver'))
.select('p').addE('works_for').property('AccessLevel','NonAdmin').to(g.V().hasLabel('Office').has('Name', 'New York'))
.select('p').addE('works_for').property('AccessLevel','NonAdmin').to(g.V().hasLabel('Office').has('Name', 'Boston'))
.select('p').addE('works_for').property('AccessLevel','Admin').to(g.V().hasLabel('Office').has('Name', 'Orlando'))
.select('ow1').addE('master_of').to(g.V().hasLabel('Office').has('Name','Portland'))
.select('ow1').addE('master_of').to(g.V().hasLabel('Office').has('Name','Vancouver'))
.select('oe1').addE('master_of').to(g.V().hasLabel('Office').has('Name','Boston'))
.select('oe1').addE('master_of').to(g.V().hasLabel('Office').has('Name','Orlando'))

Update: I had one of my edges missing a 'AccessLevel' property that .by() was referencing. Without that property query was failing. Using coalesce() I set it to a "foobar" value before anything else:

outE("works_for").as("e").coalesce(values('AccessLevel'), property('AccessLevel','foo')).select('e').

Upvotes: 1

Views: 345

Answers (1)

Daniel Kuppitz
Daniel Kuppitz

Reputation: 10904

Does your query (the one, that creates the graph) really work in CosmosDB? If others want to follow along, here's the query that creates the graph without nested g.V()'s:

g.addV('Office').property('Name','Seattle').property('IsMaster','true').as('ow1').
  addV('Office').property('Name','Portland').property('IsMaster','false').as('ow2').
  addV('Office').property('Name','Vancouver').property('IsMaster','false').as('ow3').
  addV('Office').property('Name','New York').property('IsMaster','true').as('oe1').
  addV('Office').property('Name','Boston').property('IsMaster','false').as('oe2').
  addV('Office').property('Name','Orlando').property('IsMaster','false').as('oe3').
  addV('Person').property('Name','Zodiak').as('p').
  addE('works_for').property('AccessLevel','Admin').to('ow1').select('p').
  addE('works_for').property('AccessLevel','NonAdmin').to('ow2').select('p').
  addE('works_for').property('AccessLevel','Admin').to('ow3').select('p').
  addE('works_for').property('AccessLevel','NonAdmin').to('oe1').select('p').
  addE('works_for').property('AccessLevel','NonAdmin').to('oe2').select('p').
  addE('works_for').property('AccessLevel','Admin').to('oe3').select('ow1').
  addE('master_of').to('ow2').select('ow1').
  addE('master_of').to('ow3').select('oe1').
  addE('master_of').to('oe2').select('oe1').
  addE('master_of').to('oe3')

And the query to produce the desired result:

g.V().has("Person","Name","Zodiak").as("p").
  outE("works_for").as("e").
  inV().has("IsMaster","true").
  project("Master","Subs").
    by(project("Name","AccessLevel").
         by("Name").
         by(select("e").by("AccessLevel"))).
    by(out("master_of").as("o").
       inE("works_for").where(outV().as("p")).
       project("Name","AccessLevel").
         by(select("o").by("Name")).
         by("AccessLevel").fold())

Upvotes: 3

Related Questions