How to generate a custom JSON output from a CosmosDB Graph using Gremlin?

Question

I'm using the CosmosDB Graph database to store the names of a few people, their marriages and the children whom they have out of the marriages. In the following diagram, you will see that the person Husband has a Child A from his first marriage and Child B from his second marriage.

       Father of Husband                 Mother of Husband        GRAND FATHER & GRAND MOTHER
                +---------------+--------------+
                            Marriage
                                |
   +------------+---------------+--------------+-----------+      FATHER & MOTHER
Ex Wife A   Marriage         Husband       Marriage      Wife B
                |                              |
            Child A                         Child B               ME & STEP BROTHERS

I want to use the GremlinAPI to generate a JSON output like the following which is a hierarchical tree structure.

How do I structure the person as a node and the relationship as an edge to convert the graph to a custom JSON output?

{
    "marriage": {
        "husband": {
            "name": "Father Of Husband"
        },
        "wife": {
            "name": "Mother Of Husband"
        }
    },
    "children": [
        {
            "marriage": {
                "husband": {
                    "name": "Husband"
                },
                "wife": {
                    "name": "Wife A"
                }
            },
            "children": [
                {
                    "marriage": {
                        "husband": {
                            "name": "Child A"
                        },
                        "wife": {
                            "name": "Unknown"
                        }
                    }
                }
            ]
        },
        {
            "marriage": {
                "husband": {
                    "name": "Husband"
                },
                "wife": {
                    "name": "Wife B"
                }
            },
            "children": [
                {
                    "marriage": {
                        "husband": {
                            "name": "Child B"
                        },
                        "wife": {
                            "name": "Unknown"
                        }
                    }
                }
            ]
        }
    ]
}

UPDATE 6/21/2019

I created the following query to create the vertex and edges:

g.V().drop()
g.addV('person').property(id, 'father_of_husband').property('name', 'Father Of Husband').property('title', 'husband')
g.addV('person').property(id, 'mother_of_husband').property('name', 'Mother Of Husband').property('title', 'wife')
g.addV('person').property(id, 'husband').property('name', 'Husband').property('title', 'husband')
g.addV('person').property(id, 'ex_wife_a').property('name', 'Ex Wife A').property('title', 'wife')
g.addV('person').property(id, 'wife_b').property('name', 'Wife B').property('title', 'wife')
g.addV('person').property(id, 'child_a').property('name', 'Child A').property('title', 'husband')
g.addV('person').property(id, 'child_b').property('name', 'Child B').property('title', 'wife')

g.addV('marriage').property(id, 'marriage_f_m').property('name', 'Marriage F & M')
g.addV('marriage').property(id, 'marriage_ex_wife_a_h').property('name', 'Marriage EWA & H')
g.addV('marriage').property(id, 'marriage_wife_b_h').property('name', 'Marriage WB & H')

g.V('father_of_husband').addE('married').to(g.V('marriage_f_m'))
g.V('mother_of_husband').addE('married').to(g.V('marriage_f_m'))

g.V('ex_wife_a').addE('married').to(g.V('marriage_ex_wife_a_h'))
g.V('husband').addE('married').to(g.V('marriage_ex_wife_a_h'))

g.V('wife_b').addE('married').to(g.V('marriage_wife_b_h'))
g.V('husband').addE('married').to(g.V('marriage_wife_b_h'))

g.V('husband').addE('child_of').to(g.V('marriage_f_m'))
g.V('child_a').addE('child_of').to(g.V('marriage_ex_wife_a_h'))
g.V('child_b').addE('child_of').to(g.V('marriage_wife_b_h'))

the diagram is nice but it would better to get some actual sample data like this example: https://stackoverflow.com/questions/51388315/gremlin-choose-one-item-at-random that way you can get a working/tested Gremlin traversal as an answer — stephen mallette, Jun 21 '19 at 13:36
I updated the question with the query that can be used to create the vertex and edges. They ran successfully in CosmosDB. I don't know there is any playground online where I can run these queries and make it easy for you to access. — wonderful world, Jun 21 '19 at 18:12
I did not find an **online playground** like **JavaScript playground** where I can run the queries. The CosmosDB Gremlin console is private. I ran some basic queries which is my level of knowledge. — wonderful world, Jun 21 '19 at 20:06
Given the data you expect to get back, I'm guessing that you want to start your traversal from a particular marriage (specifically, "marriage_f_m") - is that right? — stephen mallette, Jun 22 '19 at 09:38
also, I see that you want "marriage" keys in your result to break into "husband" and "wife" keys, but I don't see the data present to figure out how to determine which edge points to a "husband" and which points to a "wife". Like, I could determine it by the nature of the "id" or "name" because it has the string "husband" or "wife" in it, but I assume that's not what you want. Does your model/sample data need some changes there or am i just missing something? — stephen mallette, Jun 22 '19 at 09:38
Yes. The hierarchical tree has a root node which in this case is ```marriage_f_m```. The child who is born of that partnership/marriage will become a child node. Then the child born out that relationship will become another child node and finally form a hierarchical tree. — wonderful world, Jun 22 '19 at 10:35
I added the name of the generation next to the level where the marriage has taken place. — wonderful world, Jun 22 '19 at 10:41
I updated the create script by adding a new property called ```title``` which will tell whether the label called ```person``` is a ```husband``` or ```wife```. My only requirement is to generate the hierarchical tree with a root starting with grand parents marriage, then their children's marriage as a child node, then their children's inside that. I'm flexible enough to change the keys in the final JSON output. I surely want to see the nodes and edges optimized enough to save the charge involved with the cloud. — wonderful world, Jun 22 '19 at 11:01

score 1 · Accepted Answer · answered Jun 24 '19 at 15:45

I don't think there's any way to get the result structured in the format you've described. The best thing to do is probably:

get a tree containing all relationships
process this tree on the client-side and transform it into the desired structure

To get the full tree, you'd do:

g.V('marriage_f_m').
  repeat(__.both().simplePath()).
    emit().
  tree()

... or with edge labels included to simplify the restructuring:

g.V('marriage_f_m').
  repeat(__.bothE().otherV().simplePath()).
    emit().
  tree().
    by().
    by(label)

How to generate a custom JSON output from a CosmosDB Graph using Gremlin?

1 Answers1

Linked