Orientdb fetch relationship as array in json

Question

I want to fetch the vertices connected by the edge to be returned as an array as a property in json.

Eg: If a POST has 10 comments the query should return something like this.

{
    @class: Post,
    postTitle: "Some title",
    comments: [
        { 
           @class: Comment,
           content: "First Comment,
           someKey: "Some Value"  
        },
        { 
           @class: Comment,
           content: "Second Comment
           someKey: "Some Value"
        }
    ]
}

It is possible to get one property of the vertices in an array by this query

select *, out('HAS_COMMENT').content as comments from POST

This will result in an array which has only the value of 'content' property in the Comment class

I need to fetch the full record as a nested json.

UPDATE

If I just use out('HAS_COMMENT') in the query instead of out('HAS_COMMENT').content , it returns the @rid field instead of full record.

Answer 1

I tried your case with this structure :

create class Post extends V
create class Comment extends V
create class HAS_COMMENT extends E

create property Post.postTitle String
create property Comment.content String
create property Comment.someKey Integer

create vertex Post set postTitle="First"
create vertex Post set postTitle="Second"
create vertex Comment set content="First Comment", someKey="1"
create vertex Comment set content="Second Comment", someKey="2"
create vertex Comment set content="Third Comment", someKey="3"
create vertex Comment set content="Fourth Comment", someKey="4"
create vertex Comment set content="Fifth Comment", someKey="5"
create vertex Comment set content="Sixth Comment", someKey="6"
create vertex Comment set content="Seventh Comment", someKey="7"
create vertex Comment set content="Eighth Comment", someKey="8"
create vertex Comment set content="Ninth Comment", someKey="9"
create vertex Comment set content="Tenth Comment", someKey="10"
create vertex Comment set content="Eleventh Comment", someKey="11"
create vertex Comment set content="Twelfth Comment", someKey="12"
create vertex Comment set content="Thirteenth Comment", someKey="13"
create vertex Comment set content="Fourteenth Comment", someKey="14"

create edge HAS_COMMENT from (select from Post where postTitle="First") to (select from Comment where content="First Comment")
create edge HAS_COMMENT from (select from Post where postTitle="First") to (select from Comment where content="Second Comment")
create edge HAS_COMMENT from (select from Post where postTitle="First") to (select from Comment where content="Third Comment")
create edge HAS_COMMENT from (select from Post where postTitle="First") to (select from Comment where content="Fourth Comment")
create edge HAS_COMMENT from (select from Post where postTitle="First") to (select from Comment where content="Fifth Comment")
create edge HAS_COMMENT from (select from Post where postTitle="First") to (select from Comment where content="Sixth Comment")
create edge HAS_COMMENT from (select from Post where postTitle="First") to (select from Comment where content="Seventh Comment")
create edge HAS_COMMENT from (select from Post where postTitle="First") to (select from Comment where content="Eighth Comment")
create edge HAS_COMMENT from (select from Post where postTitle="First") to (select from Comment where content="Ninth Comment")
create edge HAS_COMMENT from (select from Post where postTitle="First") to (select from Comment where content="Tenth Comment")
create edge HAS_COMMENT from (select from Post where postTitle="Second") to (select from Comment where content="Eleventh Comment")
create edge HAS_COMMENT from (select from Post where postTitle="Second") to (select from Comment where content="Twelfth Comment")
create edge HAS_COMMENT from (select from Post where postTitle="Second") to (select from Comment where content="Thirteenth Comment")
create edge HAS_COMMENT from (select from Post where postTitle="Second") to (select from Comment where content="Fourteenth Comment")

To get the result you want, you can use the following query:

select expand($ris)
    let $a = (select from Post where postTitle = 'First'),
        $b = (select from Comment where in('HAS_COMMENT').postTitle in $a.postTitle),
        $ris = unionAll($a,$b)

Studio:

Console output:

----+-----+-------+---------+---------------+---------------+-------+--------------
#   |@RID |@CLASS |postTitle|out_HAS_COMMENT|content        |someKey|in_HAS_COMMENT
----+-----+-------+---------+---------------+---------------+-------+--------------
0   |#12:0|Post   |First    |[size=10]      |null           |null   |null
1   |#13:0|Comment|null     |null           |First Comment  |1      |[size=1]
2   |#13:1|Comment|null     |null           |Second Comment |2      |[size=1]
3   |#13:2|Comment|null     |null           |Third Comment  |3      |[size=1]
4   |#13:3|Comment|null     |null           |Fourth Comment |4      |[size=1]
5   |#13:4|Comment|null     |null           |Fifth Comment  |5      |[size=1]
6   |#13:5|Comment|null     |null           |Sixth Comment  |6      |[size=1]
7   |#13:6|Comment|null     |null           |Seventh Comment|7      |[size=1]
8   |#13:7|Comment|null     |null           |Eighth Comment |8      |[size=1]
9   |#13:8|Comment|null     |null           |Ninth Comment  |9      |[size=1]
10  |#13:9|Comment|null     |null           |Tenth Comment  |10     |[size=1]
----+-----+-------+---------+---------------+---------------+-------+--------------

About your question underlined in your UPDATE, to get the full record/s from the @rid you can use the expand() function.

Example:

Getting all of the comments connected with the vertex Post where postTitle = 'Second'

Query: select expand(out('HAS_COMMENT')) from Post where postTitle = 'Second'

Studio:

Console output:

----+------+-------+------------------+-------+--------------
#   |@RID  |@CLASS |content           |someKey|in_HAS_COMMENT
----+------+-------+------------------+-------+--------------
0   |#13:10|Comment|Eleventh Comment  |11     |[size=1]
1   |#13:11|Comment|Twelfth Comment   |12     |[size=1]
2   |#13:12|Comment|Thirteenth Comment|13     |[size=1]
3   |#13:13|Comment|Fourteenth Comment|14     |[size=1]
----+------+-------+------------------+-------+--------------

Hope it helps

EDITED

Query:

select *, $a as comments from Post
let $a = (select @class, content, someKey from Comment where in('HAS_COMMENT').postTitle in $parent.current.postTitle)

Studio: