Nested JSON from traversal/join

[Charles Munat]

I have User vertices and EmailAddress vertices connected by Owns edges.

I eventually want to return this information from a JSON API with the email addresses nested inside the user hashes, thus:

{

    "users": [

        {

            "name": "Joe",

            "emails": [

                {

                    "address": "j...@schmoe.com",

                    "isSubscriber": true

                    "@rid": "20:0"

                },

                {

                    "address": "j...@blow.com",

                    "isSubscriber": false

                    "@rid": "20:1"

                }

            ]

        },

        ...

    ]

}

I have come up with a query that allows me to list the emails as an array of addresses, but I want the full objects. Is there a way to do this with a single query? Can I nest further by doing further traversals? Or does this require multiple queries and then some matching up in my code?

Here's the best query I've come up with so far, but it falls short:

select *, out('Owns').address as emails from User

Thanks!

Chas.

[normanLinux]

Caveat: I do not have a lot of experience with Orient.

However, it seems strange to me that you would store your email address data as separate vertices joined by edges - unless a single email address vertex could be owned by two or more user vertices.  

Would it not make more sense for these addresses to be stored as embedded lists? This should surely permit you to retrieve a JSON result of the type you refer to (minus the rid, of course).

[Giulia Brignoli]

Hi,

when you execute this query in the browse in Visual Studio

select *, out('Owns').mail as emails, out('Owns').@rid as rid from User

simply click on spits RAW to see the result in JSON format (look the screen)

If you click on RAW this return some like this:

{

    "result": [

        {

            "@type": "d",

            "@rid": "#12:0",

            "@version": 2,

            "@class": "User",

            "name": "Giulia",

            "out_Owns": [

                "#14:0"

            ],

            "emails": [

                "g...@gmail.com"

            ],

            "rid": [

                "#13:0"

            ],

            "@fieldTypes": "out_Owns=g,rid=z"

        },

        {

            "@type": "d",

            "@rid": "#12:1",

            "@version": 2,

            "@class": "User",

            "name": "Alessandro",

            "out_Owns": [

                "#14:1"

            ],

            "emails": [

                "a...@gmail.com"

            ],

            "rid": [

                "#13:1"

            ],

            "@fieldTypes": "out_Owns=g,rid=z"

        },

        {

            "@type": "d",

            "@rid": "#12:2",

            "@version": 2,

            "@class": "User",

            "name": "Luigi",

            "out_Owns": [

                "#14:2"

            ],

            "emails": [

                "l...@gmail.com"

            ],

            "rid": [

                "#13:2"

            ],

            "@fieldTypes": "out_Owns=g,rid=z"

        }

    ],

    "notification": "Query executed in 0.016 sec. Returned 3 record(s)"

}

Regards,

Giulia

[Charles F. Munat]

Hi, Giulia,

Thank you! This "Raw" button is very useful for seeing the JSON output. I didn't know about that.

Unfortunately, that does not answer my original question. I am already able to nest the email address in an array in the User object. What I want to do is to expand the email objects themselves and nest them in an array in the User object, preferably in a single query.

In other words, if I have this:

insert into User set name = "Joe", isMale = true

insert into EmailAddress set address = "j...@mama.com", isSubscriber = true
insert into EmailAddress set address = "j...@gmail.com", isSubscriber = false

create edge Owns
    from (select from User where name = "Joe")
    to (select from EmailAddress where address = "j...@mama.com")
create edge Owns
    from (select from User where name = "Joe")
    to (select from EmailAddress where address = "j...@gmail.com")

This gives me a User vertex connected to two different EmailAddress vertices via two Owns edges.

What I want is to write a single query that returns the expanded EmailAddress objects nested in the User object:

{
  "name": "Joe",
  "isMale": true,
  "emailAddresses": [
    {
      "address": "j...@mama.com",
      "isSubscriber": true
    },
    {
      "address": "j...@gmail.com",
      "isSubscriber": false
    }
  ]
}

Does that make sense? Possible?

Thanks!
Chas.

On 8/17/15 8:26 PM, Giulia Brignoli wrote:

Hi,

when you execute this query in the browse in Visual Studio

select *, out('Owns').mail as emails, out('Owns').@rid as rid from User

simply click on spits ROW to see the result in JSON format (look the screen)

If you click on ROW this return some like this:

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/XgM8ccwkD1g/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Giulia Brignoli]

Hi Charles,

the only way I've found to do what you asked is the following:

select *, out('Owns').include('mail','isSubscriber') as Email from User

 

This query also returns a number of other fields that are required (e.g. "@type": "d", "@rid": "#12:0",  "@version": 3,  "@class": "User",) and can not be excluded from the JSON file.

This return:

"result": [

        {

            "@type": "d",

            "@rid": "#12:0",

            "@version": 3,

            "@class": "User",

            "name": "Giulia",

            "out_Owns": [

                "#14:0"

            ],

            "isMale": false,

            "Email": [

                {

                    "@type": "d",

                    "@version": 0,

                    "mail": "g...@gmail.com",

                    "isSubscriber": true

                }

            ],

            "@fieldTypes": "out_Owns=g,Email=z"

        }
]

I hope you will be fine anyway.

Regards,

Giulia

[Charles F. Munat]

Ah, excellent! As for the other fields, I just left them out to simplify the question as they're irrelevant to it. I know they're returned.

Thanks! I'll  try this.

Chas.

On 8/17/15 10:47 PM, Giulia Brignoli wrote:

Hi Charles,

the only way I've found to do what you asked is the following:

select *, out('Owns').include('mail','isSubscriber') as Email from User

 

This query also returns a number of other fields that are required (e.g. "@type": "d", "@rid": "#12:0",  "@version": 3,  "@class": "User",) and can not be excluded from the JSON file.

I hope you will be fine anyway.

Regards,

Giulia