How to filter the nested records data in select query

Hi All,

As per our project requirement and the vertex edge structure
we’re fetching the vertex property and it’s edges data in the single query but we need to filter the data of the nested edge in single select query

Please follow the below steps for the example
e.g.
Vertexes with properties :

Org - name
User - name
APP - name

Edges with in out classes

works_at
in - ORG
out - User

subscribed
in - App
out - Org

member
in - App
out - User

Dummy data insertions query:

INSERT INTO ORG (name) VALUES (“org 1”),(“org 2”), (“org 3”)
INSERT INTO app (name) VALUES (“App 1”),(“App 2”), (“App 3”)
INSERT INTO User (name) VALUES (“User 1”),(“User 2”), (“User 3”)

Edges dummy data creation:

User 1 - works_at Org 1
CREATE EDGE works_at FROM #26:0 TO #22:0
User 1 - works_at Org 2
CREATE EDGE works_at FROM #26:0 TO #23:0
User 2 - works_at Org 1
CREATE EDGE works_at FROM #27:0 TO #22:0

Org 1 Subscribed App 1
CREATE EDGE subscribed FROM #22:0 TO #30:0

Org 1 Subscribed App 2
CREATE EDGE subscribed FROM #22:0 TO #31:0

Org 2 Subscribed App 1
CREATE EDGE subscribed FROM #23:0 TO #30:0

User 1 Member of App 1
CREATE EDGE member FROM #26:0 TO #30:0

User 1 Member of App 2
CREATE EDGE member FROM #26:0 TO #31:0

User 2 Member of App 1
CREATE EDGE member FROM #27:0 TO #30:0

And finally the Select query

SELECT @rid AS rid, name, OUT(“works_at”):{ @rid AS rid, name, OUT(“subscribed”):{name, @rid AS rid} AS apps } AS worksAtOrgs
FROM USER WHERE name = “User 2”

Note:
As per the above structure, Here I need the User by name
and it’s organizations along with the organization’s subscribed apps whose provided user is part of as a member to the app

Currently as per the above select query mentioned
for the User2 it fetches the correct organization but for the organization app’s it’s fetching all apps which organization subscribed,
Instead i want only those app’s which the provided user is part of

I tried the sub queries and also referred the User’s edge **OUT(“member”) ** details in the sub query but not getting the expected results

Is there any workaround/ Projections to filter out the nested edge data for the record in select query.

Try this:

select u.name, o.name, a.name from (
    match {class: User, as: u, where: (name = 'User 2')}-works_at->{class: Org, as: o}-subscribed->{class: App, as: a},
    {as: u}-member->{as: a} return u, o, a
)

The inner match matches in the first path the organizations the user works at, with the organization’s subscribed apps, and in the second path the previously matched apps that also the user is member of.
With the select you can get the projection you need.

Thank you @johny65 for your reply,

I have one more doubt in the query for getting the results, As there are some other changes are made into structure also
Changes:

  1. Created the new owns edge between the User and the Org vertex
    (i.e. User who owned the organization as a admin )
  2. Each edge has a config property which stores some data like config consisting of roles and other information
    All the commands for the data insertions and new edges creation are listed below

As per the structure, i want the query to return all the Orgs whether user has owns or works_at edge with org and that org subscribed to the App

Please follow the below commands for the changes

  1. Remove the User 1 works_at edge with the Org 1,Org 2, Org 3
    and follow the below commands for the new edges insertions

New owns Edge (i.e user Owns the oragnization )

CREATE CLASS owns IF NOT EXISTS EXTENDS E
CREATE PROPERTY owns.in IF NOT EXISTS LINK Org
CREATE PROPERTY owns.out IF NOT EXISTS LINK User
CREATE PROPERTY owns.config IF NOT EXISTS EMBEDDEDMAP (MANDATORY TRUE, NOTNULL TRUE)

Added the Edge Proeprties

CREATE PROPERTY works_at.config IF NOT EXISTS EMBEDDEDMAP (MANDATORY TRUE, NOTNULL TRUE)
CREATE PROPERTY member.config IF NOT EXISTS EMBEDDEDMAP (MANDATORY TRUE, NOTNULL TRUE)

New orgs added for the user 2 and Owns edge created for user 1 and 2

INSERT INTO ORG (name) VALUES (“org 4”),(“org 5”)

User 1 owns org 1
CREATE EDGE owns FROM #26:0 TO #22:0 SET config = {}
User 1 owns org 2
CREATE EDGE owns FROM #26:0 TO #23:0 SET config = {}
User 1 owns org 3
CREATE EDGE owns FROM #26:0 TO #24:0 SET config = {}

User 2 owns org 4
CREATE EDGE owns FROM #27:0 TO #25:0 SET config = {}
User 2 owns org 5
CREATE EDGE owns FROM #27:0 TO #22:1 SET config = {}

Created new Orgs Subscribed Edge

Org 4 Subscribed App 1
CREATE EDGE subscribed FROM #25:0 TO #30:0
Org 5 Subscribed App 1
CREATE EDGE subscribed FROM #22:1 TO #30:0

User 2 Member edge creation

CREATE EDGE member FROM #27:0 TO #30:0

All edges properties updated

UPDATE EDGE owns SET config = {
“role”:“admin”
}

UPDATE EDGE works_at SET config = {
“role”:“employee”
}

UPDATE EDGE member SET config = {
“role”:“user”
}

Can you suggest the changes for the same,

select u.name, o.name, a.name from (
    match {class: User, as: u, where: (name = 'User 2')}-works_at->{class: Org, as: o}-subscribed->{class: App, as: a},
    {as: u}-member->{as: a} return u, o, a
)

i.e To get the owns edge and the works_at edge both mappings in the single query
Also i need the edges properties also in the select query result (i.e config property of member, owns , works_at edges)

I tried one Query to get the both works_at and Owns edges organization and then to get their subscribed App’s

select u.name, o.name, a.name from (
match {class: User, as: u, where: (name = ‘User 1’)}.outE(){as: oo, where: (@class = ‘owns’ OR @class = ‘works_at’)}
.in{class : org, as:o}-subscribed->{class: App, as: a},
{as: u}-member->{as: a} return u, o, a
)

It returns the data but if user does not have any organizations then it does not returns any record,
Is their any suggestion or workaround in the above query to get the user record even if no organizations has a edge with that user.

Hello @johny65,

i have updated the query as per our requirement to get the edges properties data also

 select u.name, oo.@class as orgRole, o.name AS Org, a.name, ac.config AS appConfig from (
    		match {class: User, as: u, where: (name = "User 2")}
    		.outE(){as: oo, where: (@class = "owns" OR @class = "works_at")}
    		.in{class : org, as:o}-subscribed->{class: App, as: a},
    		{as: u}.outE("member"){as: ac}.in{as: a}
    		return u, oo, o, a, ac
    	)

But when no user is not the part of any organization, Or he’s part if the organization but is not the member of the app then no result is returned.
can you suggest the query changes or workaround to get the user record in return and to avoid the query filters when no organization found for user or he’s not member of the app

Thank You.