OrientDB: can't select data from connected document

Question

I'm trying to execute a simple sql query to retrieve data from the document searched as well as few properties of a connected document. But, for some reason ran into a dead wall on how to select the property from the connected document. Here is my simple scenario:

I have 2 documents, an Account and a User. Account document has an edge to a User named 'Employs'. I'm trying to login a user by email and password. If record for the user is found, I simply need to get some user data and few properties from an account document to be stored in user's session.

Here is my query:

try (ODatabaseDocumentTx db = DbPool.getConnection()) {
    String sql
            = " select @rid.asString() as userRid, firstName, lastName, "
            + "     active as userActive, in('Employs') as account "
            + "from User "
            + "where email = ? and password = ?";

    OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<>(sql);
    List<ODocument> result = db.command(query).execute('[email protected]', 'abc');

    ODocument d = result.get(0);

    System.out.println("1 "+ d.field("account.id"));
    System.out.println("2 "+ d.field("userRid"));
    System.out.println("3 "+ d.field("account.company"));
    System.out.println("4 "+ d.field("firstName"));
    System.out.println("5 "+ d.field("lastName"));
    System.out.println("6 "+ d.field("account.active"));
    System.out.println("7 "+ d.field("userActive"));

    return new SessionUser(
            d.field("account.id"), 
            d.field("userRid"), 
            d.field("account.company"),
            d.field("firstName"), 
            d.field("lastName"),
            d.field("account.active"),
            d.field("userActive"));
}

It fails to create the SessionUser object. More specifically, it fails on retrieval of the account properties. Here is how the data looks in System.out:

1 [17]
2 #37:0
3 [Seller 1]
4 Mike
5 Maloney
6 [true]
7 true
WARN : 2016-11-21 17:53:53,036 WARN c.t.e.k.c.ExceptionHandler - Error: java.util.LinkedHashSet cannot be cast to java.lang.Integer

Here is how a single account.id property looks like in the debugger

I do see that the account properties are coming in as objects, just can't figure out how to select them easy. I didn't want to go through manually casting list then set, etc. to get to a simple account.id element. I'm sure there is a straight forward way, just can't see it.

What is the right way to select data of a connected document? Or, is there a better way to construct the query itself?

Thanks.

Answer 1

I've answered you on Google Groups https://groups.google.com/d/msg/orient-database/ta4izTYq-CY/l5tHPOu5CgAJ