Matching nested data for a GraphQL query

I’ve just started writing a tool to convert GraphQL queries into OrientDB SQL to improve development experience and reduce the total number of queries per GraphQL operation. I’ve previously had success in this area when I used Neo4J and created graphql-cypher.

I’m excited about using OrientDB because the query language seems to be far more powerful than Cypher, since it’s built on SQL. However, I’m stumped on how to properly structure arbitrarily nested queries to match the GraphQL data shape.

For instance, suppose I have a GraphQL query like so against the demodb data:

query GetProfileAndFriends {
  profile(input: { id: 1}) {
    Id
    Name
    friends {
      Id
      Name
    }
  }
}

I’ve gotten the basics of the MATCH command, and I’ve figured out enough about projections to make a simple testing query:

SELECT profile:{Id, Name}, friends:{Id, Name} FROM (
  MATCH {class: Profiles, where: (Id=1), as: profile},
  	{as: profile}-HasFriend-{as: friends}
  RETURN $matches
)

But this query isn’t sufficient for a few reasons.

One, the result set is ‘flat’, so I get distinct rows returned for each combination of Profile->Friend relationships, where I want to actually receive an object like {Id, Name, friends: [{Id, Name} ...]}. I haven’t seen an obvious way with projections to aggregate the friends together as a nested value per-profile like this.

Two, I’m not sure how to extend this to arbitrary nested relationships. For instance, traversing out from Profile to Customer for friends. As the query was extended with further nested relationships, I’d also want that data to be structured in a nested fashion in the final result, i.e. {Id, Name, friends: [{Id, Name, customer: {Id, ... }...]}

Three, I don’t think this pattern actually aligns with what I want to match. If a Profile doesn’t have any friend relationships, for instance, I still want it to be returned in the result set (not omitted because it lacks the required relationship for the pattern). I know I can use optional to do this, but that doesn’t work so well with my intent of dynamically building these sub-query blocks based on a GraphQL query operation.

The actual nested structure of the data may not be necessary to do at the database query level; I could potentially process the result to generate the correct structure after the fact. But, it would be much simpler for me to properly encode the structure in the query result if possible.

Thanks!

Hi @a-type

i think in this specific case you can use only select with nested projection in order to shape the result set as you need, with something like this

select Id, Name, out('HasFriend'):{Id,Name} as friends from Profiles where Id = 1

Nested projections works also on out/in graph functions for traversing

Let me know if this helps

I’d prefer to use MATCH if possible since it aligns better to the way I want to express the query in GraphQL, but that’s helpful for getting started.

Suppose I also wanted to optionally match a relationship out of friends if it existed… as the nesting grows deeper, I think MATCH seems to be a bit more expressive. But since my goal is to generate this code, perhaps it doesn’t matter that much.

@a-type

ok i got it. Match is indeed more powerful for traversal. You could use group by with list/set function in order to get the same result.

Something like this

SELECT profile:{Id, Name}, friends:{Id, Name} FROM (
  MATCH {class: Profiles, where: (Id=1), as: profile},
  	{as: profile}-HasFriend-{as: friends}
  RETURN profile, list(friends) as friends
  GROUP By profile
)

Thanks, that’s much closer to what I want.

This may be more of a SQL problem at this point, but now I’m just trying to figure out how to nest multiple levels of related queries. For instance, if I went on to also try to fetch the Customer classes which were connected to friends of the original profile. It seems that I’d need a new level in the query which matches from friends, traverses the HasCustomer edge to get the Customer, and then does another group by according to the friend it was traversing. Seems like a whole subquery, perhaps…