Skip to content

SQL: How to filter using edge properties? #959

Closed

Description

@darpan-vyas

ArcadeDB Version: ArcadeDB Server v23.3.1-SNAPSHOT (build 143b1a6/1677646375828/main)

JDK Version: JMX Metrics

OS: MacOS Ventura 13.2.1

Expected behavior

Filter all nodes where the connected edge property satisfies mentioned condition

Actual behavior

Nothing is returned.

My intent was to filter all dataset-type nodes that were accessed by users of the 'accounts' team recently. The recent condition is specified using an edge property 'timestamp' defined under edge-type 'access'. The said edge connected users with datasets.

Example:
(u109)-[:access {timestamp: 1679286814}]->(d9)
The above statement indicates that user 'u109' accessed dataset 'd14' at timestamp '1679250814'

image

PS: I used Cypher to create the DB and SQL to query it

In other words, the idea was to answer below question:

'Which data assets are most frequently accessed by downstream data consumers of the accounts team?'

Query:
match{type:team, as:a, where: (name='accounts')}. in('in_team'){type:user, as:b}. out('access'){type:dataset, as:c, where: (access.timestamp>1679261614)} return a,b,c

image

Steps to reproduce

  • The database contains multiple types of nodes (dataset, job, user, tag, team, stack, datasource, etc). These nodes are connected by various edges with each other (in_use, has_created, in_team, access, etc).
  • I connected the database in the console and queried it to answer the above-mentioned question.

Activity

  1. self-assigned this
    on Mar 20, 2023
  2. lvca commented on Mar 20, 2023

    @lvca
    Member

    Could you please provide a sample dataset to run some queries? It doesn't matter if the actual data is real it's just to understand how data is connected

  3. darpan-vyas commented on Mar 20, 2023

    @darpan-vyas
    Author

    Sure, Luca! Please find the below Cypher query to create the DB.

    CREATE (d1:dataset {name:'d1',created_on:20220101,latest_data_ingestion:1678037040,latest_data_quality_run:1678046918,data_quality_streak:2,query_count_7d:1000,user_base_7d:10}), (d2:dataset {name:'d2',created_on:20220201,latest_data_ingestion:1678044240,latest_data_quality_run:1678054118,data_quality_streak:3,query_count_7d:1000,user_base_7d:1}), (d3:dataset {name:'d3',created_on:20220301,latest_data_ingestion:1678051440,latest_data_quality_run:1678061318,data_quality_streak:4,query_count_7d:1000,user_base_7d:19}), (d4:dataset {name:'d4',created_on:20220401,latest_data_ingestion:1678058640,latest_data_quality_run:1678068518,data_quality_streak:-3,query_count_7d:10000,user_base_7d:18}), (d5:dataset {name:'d5',created_on:20220501,latest_data_ingestion:1678065840,latest_data_quality_run:1678075718,data_quality_streak:-4,query_count_7d:1500,user_base_7d:2}), (d6:dataset {name:'d6',created_on:20220601,latest_data_ingestion:1678073040,latest_data_quality_run:1678082918,data_quality_streak:-5,query_count_7d:18000,user_base_7d:7}), (d7:dataset {name:'d7',created_on:20220701,latest_data_ingestion:1678080240,latest_data_quality_run:1678090118,data_quality_streak:-6,query_count_7d:500,user_base_7d:29}), (d8:dataset {name:'d8',created_on:20220801,latest_data_ingestion:1678087440,latest_data_quality_run:1678097318,data_quality_streak:-2,query_count_7d:7691,user_base_7d:78}), (d9:dataset {name:'d9',created_on:20220901,latest_data_ingestion:1678094640,latest_data_quality_run:1678104518,data_quality_streak:1,query_count_7d:100,user_base_7d:4}), (d10:dataset {name:'d10',created_on:20221001,latest_data_ingestion:1678101840,latest_data_quality_run:1678111718,data_quality_streak:9,query_count_7d:10000,user_base_7d:11}), (d11:dataset {name:'d11',created_on:20221101,latest_data_ingestion:1678109040,latest_data_quality_run:1678118918,data_quality_streak:2,query_count_7d:1000,user_base_7d:21}), (d12:dataset {name:'d12',created_on:20221201,latest_data_ingestion:1678116240,latest_data_quality_run:1678126118,data_quality_streak:3,query_count_7d:1000,user_base_7d:5}), (d13:dataset {name:'d13',created_on:20230101,latest_data_ingestion:1678123440,latest_data_quality_run:1678133318,data_quality_streak:1,query_count_7d:1000,user_base_7d:3}), (d14:dataset {name:'d14',created_on:20230201,latest_data_ingestion:1678130640,latest_data_quality_run:1678140518,data_quality_streak:1,query_count_7d:1000,user_base_7d:6}), (d15:dataset {name:'d15',created_on:20230301,latest_data_ingestion:1678137840,latest_data_quality_run:1678147718,data_quality_streak:1,query_count_7d:1000,user_base_7d:9}), (u1:user {name: 'u1'}), (u2:user {name: 'u2'}), (u3:user {name: 'u3'}), (u4:user {name: 'u4'}), (u5:user {name: 'u5'}), (j1:job{name:'j1',type:'batch'}), (j2:job{name:'j2',type:'batch'}), (j3:job{name:'j3',type:'batch'}), (j4:job{name:'j4',type:'batch'}), (j5:job{name:'j5',type:'batch'}), (j6:job{name:'j6',type:'batch'}), (j7:job{name:'j7',type:'batch'}), (j8:job{name:'j8',type:'stream'}), (j9:job{name:'j9',type:'stream'}), (j10:job{name:'j10',type:'stream'}), (a:tag {name: 'tier:gold'}), (b:tag {name: 'tier:silver'}), (c:tag {name: 'tier:bronze'}), (finance:tag {name: 'finance'}), (sales:tag {name: 'sales'}), (revenue:tag {name: 'revenue'}), (customer:tag {name: 'customer'}), (accounts:team {name:'accounts'}), (DE:team {name:'DE'}), (marketing:team {name:'marketing'}), (ds1:datasource { name:'ds1'}), (ds2:datasource { name:'ds2'}), (ds3:datasource { name:'ds3'}), (ds4:datasource { name:'ds4'}), (ds5:datasource { name:'ds5'}), (s1:stack { name:'flare'}), (s2:stack { name:'benthos'}), (d1)-[:has_tag]->(a), (d2)-[:has_tag]->(b), (d3)-[:has_tag]->(c), (d4)-[:has_tag]->(a), (d5)-[:has_tag]->(b), (d6)-[:has_tag]->(c), (d7)-[:has_tag]->(a), (d8)-[:has_tag]->(b), (d9)-[:has_tag]->(c), (d10)-[:has_tag]->(a), (d11)-[:has_tag]->(b), (d12)-[:has_tag]->(c), (d13)-[:has_tag]->(a), (d14)-[:has_tag]->(b), (d15)-[:has_tag]->(c), (d1)-[:has_tag]->(finance), (d2)-[:has_tag]->(finance), (d3)-[:has_tag]->(sales), (d4)-[:has_tag]->(finance), (d5)-[:has_tag]->(revenue), (d6)-[:has_tag]->(revenue), (d7)-[:has_tag]->(finance), (d8)-[:has_tag]->(finance), (d9)-[:has_tag]->(revenue), (d10)-[:has_tag]->(revenue), (d11)-[:has_tag]->(sales), (d12)-[:has_tag]->(sales), (d13)-[:has_tag]->(customer), (d14)-[:has_tag]->(sales), (d15)-[:has_tag]->(customer), (d1)-[:in_use]->(j1), (d8)-[:in_use]->(j1), (j1)-[:has_created]->(d2), (d2)-[:in_use]->(j2), (d7)-[:in_use]->(j2), (j2)-[:has_created]->(d15), (d3)-[:in_use]->(j3), (d6)-[:in_use]->(j3), (j3)-[:has_created]->(d14), (d4)-[:in_use]->(j4), (d5)-[:in_use]->(j4), (j4)-[:has_created]->(d15), (d10)-[:in_use]->(j5), (d9)-[:in_use]->(j5), (j5)-[:has_created]->(d15), (d12)-[:in_use]->(j6), (d11)-[:in_use]->(j6), (j6)-[:has_created]->(d4), (d14)-[:in_use]->(j7), (d13)-[:in_use]->(j7), (j7)-[:has_created]->(d10), (j8)-[:has_created]->(d7), (j9)-[:has_created]->(d9), (j9)-[:has_created]->(d10), (j10)-[:has_created]->(d5), (d1)-[:has_source]->(ds1), (d2)-[:has_source]->(ds1), (d3)-[:has_source]->(ds1), (d4)-[:has_source]->(ds2), (d5)-[:has_source]->(ds2), (d6)-[:has_source]->(ds2), (d7)-[:has_source]->(ds3), (d8)-[:has_source]->(ds3), (d9)-[:has_source]->(ds3), (d10)-[:has_source]->(ds4), (d11)-[:has_source]->(ds4), (d12)-[:has_source]->(ds4), (d13)-[:has_source]->(ds5), (d14)-[:has_source]->(ds5), (d15)-[:has_source]->(ds5), (u1)-[:own]->(d11), (u2)-[:own]->(d12), (u3)-[:own]->(d13), (u4)-[:own]->(d14), (u5)-[:own]->(d15), (u1)-[:in_team]->(DE), (u2)-[:in_team]->(DE), (u3)-[:in_team]->(DE), (u4)-[:in_team]->(DE), (u5)-[:in_team]->(DE), (j1)-[:has_stack]->(s1), (j2)-[:has_stack]->(s1), (j3)-[:has_stack]->(s1), (j4)-[:has_stack]->(s1), (j5)-[:has_stack]->(s1), (j6)-[:has_stack]->(s1), (j7)-[:has_stack]->(s1), (j8)-[:has_stack]->(s2), (j9)-[:has_stack]->(s2), (j10)-[:has_stack]->(s2), (u1)-[:own]->(d1), (u2)-[:own]->(d2), (u3)-[:own]->(d3), (u4)-[:own]->(d4), (u5)-[:own]->(d5), (u1)-[:own]->(d6), (u2)-[:own]->(d7), (u3)-[:own]->(d8), (u4)-[:own]->(d9), (u5)-[:own]->(d10), (u101:user {name:'u101'}), (u102:user {name:'u102'}), (u103:user {name:'u103'}), (u104:user {name:'u104'}), (u105:user {name:'u105'}), (u106:user {name:'u106'}), (u107:user {name:'u107'}), (u108:user {name:'u108'}), (u109:user {name:'u109'}), (u110:user {name:'u110'}), (u111:user {name:'u111'}), (u112:user {name:'u112'}), (u113:user {name:'u113'}), (u114:user {name:'u114'}), (u115:user {name:'u115'}), (u116:user {name:'u116'}), (u117:user {name:'u117'}), (u118:user {name:'u118'}), (u119:user {name:'u119'}), (u120:user {name:'u120'}), (u101)-[:access {timestamp: 1679301214}]->(d1), (u102)-[:access {timestamp: 1679299414}]->(d2), (u103)-[:access {timestamp: 1679297614}]->(d3), (u104)-[:access {timestamp: 1679295814}]->(d4), (u105)-[:access {timestamp: 1679294014}]->(d5), (u106)-[:access {timestamp: 1679292214}]->(d6), (u107)-[:access {timestamp: 1679290414}]->(d7), (u108)-[:access {timestamp: 1679288614}]->(d8), (u109)-[:access {timestamp: 1679286814}]->(d9), (u110)-[:access {timestamp: 1679285014}]->(d10), (u111)-[:access {timestamp: 1679283214}]->(d11), (u112)-[:access {timestamp: 1679281414}]->(d12), (u113)-[:access {timestamp: 1679279614}]->(d13), (u114)-[:access {timestamp: 1679277814}]->(d14), (u115)-[:access {timestamp: 1679276014}]->(d15), (u116)-[:access {timestamp: 1679274214}]->(d1), (u117)-[:access {timestamp: 1679272414}]->(d2), (u118)-[:access {timestamp: 1679270614}]->(d3), (u119)-[:access {timestamp: 1679268814}]->(d4), (u120)-[:access {timestamp: 1679267014}]->(d5), (u101)-[:access {timestamp: 1679265214}]->(d6), (u102)-[:access {timestamp: 1679263414}]->(d7), (u103)-[:access {timestamp: 1679261614}]->(d8), (u104)-[:access {timestamp: 1679259814}]->(d9), (u105)-[:access {timestamp: 1679258014}]->(d10), (u106)-[:access {timestamp: 1679256214}]->(d11), (u107)-[:access {timestamp: 1679254414}]->(d12), (u108)-[:access {timestamp: 1679252614}]->(d13), (u109)-[:access {timestamp: 1679250814}]->(d14), (u110)-[:access {timestamp: 1679249014}]->(d15), (u111)-[:access {timestamp: 1679247214}]->(d15), (u112)-[:access {timestamp: 1679245414}]->(d15), (u113)-[:access {timestamp: 1679243614}]->(d15), (u114)-[:access {timestamp: 1679241814}]->(d15), (u115)-[:access {timestamp: 1679240014}]->(d15), (u116)-[:access {timestamp: 1679238214}]->(d15), (u117)-[:access {timestamp: 1679236414}]->(d15), (u118)-[:access {timestamp: 1679234614}]->(d15), (u119)-[:access {timestamp: 1679232814}]->(d15), (u120)-[:access {timestamp: 1679231014}]->(d15), (u101)-[:in_team]->(accounts), (u102)-[:in_team]->(accounts), (u104)-[:in_team]->(accounts), (u107)-[:in_team]->(accounts), (u108)-[:in_team]->(accounts), (u116)-[:in_team]->(accounts), (u117)-[:in_team]->(accounts), (u119)-[:in_team]->(accounts), (u103)-[:in_team]->(marketing), (u105)-[:in_team]->(marketing), (u106)-[:in_team]->(marketing), (u109)-[:in_team]->(marketing), (u110)-[:in_team]->(marketing), (u111)-[:in_team]->(marketing), (u112)-[:in_team]->(marketing), (u113)-[:in_team]->(accounts), (u114)-[:in_team]->(accounts), (u115)-[:in_team]->(accounts), (u118)-[:in_team]->(accounts), (u120)-[:in_team]->(accounts)

  4. lvca commented on Mar 20, 2023

    @lvca
    Member

    Got it. You should filter by the timestamp in the edge, not in the vertex. Try this:

    match{type:team, as:a, where: (name='accounts')}
    .in('in_team'){type:user, as:b}
    .outE('access'){where: (timestamp>1679261614)}
    .inV(){type:dataset, as:c}
    return a,b,c
    
  5. darpan-vyas commented on Mar 20, 2023

    @darpan-vyas
    Author

    This worked. Thanks a lot, Luca!

  6. added this to the 23.3.1 milestone on Mar 20, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

questionFurther information is requested

Type

No type

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions