How to do left join in Azure Cosmos DB using gremlin API

Question

Im studying Gremlin API of Azure Cosmos DB and im trying to translate some SQL statements to Gremlin traversals.

Commands below:


//add product vertex
g.addV('product').property('id', 'product1')
g.addV('product').property('id', 'product2')
g.addV('product').property('id', 'product3')
g.addV('product').property('id', 'product4')
g.addV('product').property('id', 'product5')

//add product_category vertex
g.addV('product_category').property('id', 'category1')
g.addV('product_category').property('id', 'category2')

//connect product and product_categories

g.V('product1').addE('belongs_to').to(g.V('category1'))
g.V('product2').addE('belongs_to').to(g.V('category1'))
g.V('product3').addE('belongs_to').to(g.V('category2'))
g.V('product4').addE('belongs_to').to(g.V('category2'))
g.V('product5').addE('belongs_to').to(g.V('category2'))

//add image vertex
g.addV('image').property('public_url', 'url_1').property('id', 'image1')
g.addV('image').property('public_url', 'url_2').property('id', 'image2')


//link products to images

g.V('product1').addE('belongs_to').property('primary', true).to(g.V('image1'))
g.V('product2').addE('belongs_to').property('primary', true).to(g.V('image2'))

Now as you can see not all products have images.

I want to be able to select all the products with category and image property. If a product doesnt have an image, it should still be selected with a null image property.

Im trying to do something similar to left join by following this link: http://sql2gremlin.com/#_left_join

But unfortunately Gremlin API of Azure Cosmos does not yet support match() step.

My current query only selects products that have outgoing edge to image:

g.V()
.has('label', 'product')
.as('product')
.outE('has_image')
.has('primary', true)
.inV()
.as('primary_image')
.in('has_image')
.out('belongs_to')
.as('category')
.select('product','primary_image','category')
.by(__.valueMap()).by(__.values('public_url')).by(__.values('name'))

an example would be very useful to improve the quality of the question. Show us a sample of the kind the data you have, the edges and vertices you have and the traversal you are trying to run. — AlexDrenea
Thank you for your correction, Please see the editted version. — ephraim lambarte

bechbd bechbd · Accepted Answer · 2019-10-28T05:22:54

So there are a couple of things going on here:

1) The script you had above didn't work correctly to insert the data into a TinkerGraph (I don't have a Cosmos account). I have added the updated script below.

2) What you are trying to doesn't need a left join. Since these are both relationships off the product you can just project your results from the product vertices as shown here:

g.V().
  hasLabel('product').
  project('product', 'category', 'image').
    by(id()).
    by(out('belongs_to').id()).
    by(
      __.out('has_image').fold().
      coalesce(
        unfold().id(), 
        constant('No Image')
      )
    )

This works by finding all product vertices and then returning the image and category

3) The coalesce statement there is based on this Gremlin recipe to check for element existence. If it does not find anything, because a recipe doesn't exist it returns a constant value

4) Gremlin doesn't allow for returning a null values so you need to return something

Updated Add Script

//add product vertex
g.addV('product').property(id, 'product1')
g.addV('product').property(id, 'product2')
g.addV('product').property(id, 'product3')
g.addV('product').property(id, 'product4')
g.addV('product').property(id, 'product5')

//add product_category vertex
g.addV('product_category').property(id, 'category1')
g.addV('product_category').property(id, 'category2')

//connect product and product_categories

g.V('product1').addE('belongs_to').to(g.V('category1'))
g.V('product2').addE('belongs_to').to(g.V('category1'))
g.V('product3').addE('belongs_to').to(g.V('category2'))
g.V('product4').addE('belongs_to').to(g.V('category2'))
g.V('product5').addE('belongs_to').to(g.V('category2'))

//add image vertex
g.addV('image').property(id, 'image1').property('public_url', 'url_1')
g.addV('image').property(id, 'image2').property('public_url', 'url_2')


//link products to images

g.V('product1').addE('has_image').to(V('image1')).property('primary', true)
g.V('product2').addE('has_image').to(V('image2')).property('primary', true)

How to do left join in Azure Cosmos DB using gremlin API

1 Answers