weird behavior with SELECT

[ehsanonline]

I made two docs : user:tobie with name="tobie" and article:surreal, with title="good" then I made 4 relations between them (not unique) : user:tobie->write->article:surreal , two of those relations have SET time.written = time::now().
I wanted to retrieve them all as one result, meaning in one JSON having all docs with relations and all properties (name, title, time.written) nested. how can I do that?
I tried these and find out some new/weird things about SurrQL that are different from SQL:
selecting sequence in SELECT statement changes the result, for example these two SurrQL returns different result:

SELECT *,<-write<-user.*,<-write.* FROM article:surreal;
SELECT *,<-write.*,<-write<-user.* FROM article:surreal;

the first query result is:

[
  {
    "result": [
      {
        "<-write": [
          {
            "id": "write:ahtwyjik0ceqd8bgr8f1",
            "in": "user:tobie",
            "out": "article:surreal"
          },
          {
            "id": "write:rpngdgkq4of4qla5crya",
            "in": "user:tobie",
            "out": "article:surreal",
            "time": {
              "written": "2022-08-23T04:34:37.981815668Z"
            }
          },
          {
            "id": "write:sd30wmksknj8z36l3dx7",
            "in": "user:tobie",
            "out": "article:surreal",
            "time": {
              "written": "2022-08-23T04:37:31.081179605Z"
            }
          },
          {
            "id": "write:svdraa53r7ajbujqti31",
            "in": "user:tobie",
            "out": "article:surreal"
          }
        ],
        "id": "article:surreal",
        "title": "good"
      }
    ],
    "status": "OK",
    "time": "222.435µs"
  }
]

the second query result is:

[                                                                              
  {                                                                            
    "result": [                                                                
      {                                                                        
        "<-write": [                                                           
          {                                                                    
            "<-user": [                                                        
              {                                                                
                "id": "user:tobie",                                            
                "name": "tobie"                                                
              },                                                               
              {                                                                
                "id": "user:tobie",                                            
                "name": "tobie"                                                
              },                                                               
              {                                                                
                "id": "user:tobie",                                            
                "name": "tobie"                                                
              },                                                               
              {                                                                
                "id": "user:tobie",                                            
                "name": "tobie"                                                
              }                                                                
            ],                                                                 
            "id": "write:ahtwyjik0ceqd8bgr8f1",                                
            "in": "user:tobie",                                                
            "out": "article:surreal"                                           
          },                                                                   
          {                                                                    
            "<-user": [                                                        
              {                                                                
                "id": "user:tobie",                                            
                "name": "tobie"                                                
              },                                                               
              {                                                                
                "id": "user:tobie",                                            
                "name": "tobie"                                                
              },                                                               
              {                                                                
                "id": "user:tobie",                                            
                "name": "tobie"                                                
              },                                                               
              {                                                                
                "id": "user:tobie",                                            
                "name": "tobie"                                                
              }                                                                
            ],                                                                 
            "id": "write:rpngdgkq4of4qla5crya",                                
            "in": "user:tobie",                                                
            "out": "article:surreal",                                          
            "time": {                                                          
              "written": "2022-08-23T04:34:37.981815668Z"                      
            }                                                                  
          },                                                                   
          {                                                                    
            "<-user": [                                                        
              {                                                                
                "id": "user:tobie",                                            
                "name": "tobie"                                                
              },                                                               
              {                                                                
                "id": "user:tobie",                                            
                "name": "tobie"                                                
              },                                                               
              {                                                                
                "id": "user:tobie",                                            
                "name": "tobie"                                                
              },                                                               
              {                                                                
                "id": "user:tobie",                                            
                "name": "tobie"                                                
              }                                                                
            ],                                                                 
            "id": "write:sd30wmksknj8z36l3dx7",                                
            "in": "user:tobie",                                                
            "out": "article:surreal",                                          
            "time": {                                                          
              "written": "2022-08-23T04:37:31.081179605Z"                      
            }                                                                  
          },                                                                   
          {                                                                    
            "<-user": [                                                        
              {                                                                
                "id": "user:tobie",                                            
                "name": "tobie"                                                
              },                                                               
              {                                                                
                "id": "user:tobie",                                            
                "name": "tobie"                                                
              },                                                               
              {                                                                
                "id": "user:tobie",                                            
                "name": "tobie"                                                
              },                                                               
              {                                                                
                "id": "user:tobie",                                            
                "name": "tobie"                                                
              }                                                                
            ],                                                                 
            "id": "write:svdraa53r7ajbujqti31",                                
            "in": "user:tobie",                                                
            "out": "article:surreal"                                           
          }                                                                    
        ],                                                                     
        "id": "article:surreal",                                               
        "title": "good"                                                        
      }                                                                        
    ],                                                                         
    "status": "OK",                                                            
    "time": "231.269µs"                                                        
  }                                                                            
]                                                                              

I think it replaces new properties into the previous one? , so I tried this:

 SELECT *,<-write.*,<-write.in.* FROM article:surreal;

 [
  {
    "result": [
      {
        "<-write": [
          {
            "id": "write:ahtwyjik0ceqd8bgr8f1",
            "in": [
              {
                "id": "user:tobie",
                "name": "tobie"
              },
              {
                "id": "user:tobie",
                "name": "tobie"
              },
              {
                "id": "user:tobie",
                "name": "tobie"
              },
              {
                "id": "user:tobie",
                "name": "tobie"
              }
            ],
            "out": "article:surreal"
          },
          {
            "id": "write:rpngdgkq4of4qla5crya",
            "in": [
              {
                "id": "user:tobie",
                "name": "tobie"
              },
              {
                "id": "user:tobie",
                "name": "tobie"
              },
              {
                "id": "user:tobie",
                "name": "tobie"
              },
              {
                "id": "user:tobie",
                "name": "tobie"
              }
            ],
            "out": "article:surreal",
            "time": {
              "written": "2022-08-23T04:34:37.981815668Z"
            }
          },
          {
            "id": "write:sd30wmksknj8z36l3dx7",
            "in": [
              {
                "id": "user:tobie",
                "name": "tobie"
              },
              {
                "id": "user:tobie",
                "name": "tobie"
              },
              {
                "id": "user:tobie",
                "name": "tobie"
              },
              {
                "id": "user:tobie",
                "name": "tobie"
              }
            ],
            "out": "article:surreal",
            "time": {
              "written": "2022-08-23T04:37:31.081179605Z"
            }
          },
          {
            "id": "write:svdraa53r7ajbujqti31",
            "in": [
              {
                "id": "user:tobie",
                "name": "tobie"
              },
              {
                "id": "user:tobie",
                "name": "tobie"
              },
              {
                "id": "user:tobie",
                "name": "tobie"
              },
              {
                "id": "user:tobie",
                "name": "tobie"
              }
            ],
            "out": "article:surreal"
          }
        ],
        "id": "article:surreal",
        "title": "good"
      }
    ],
    "status": "OK",
    "time": "152.997µs"
  }
]

I tried FETCH too, same result as above. How can I prevent <-write.in to repeat user 4 times? I know it's relevant with my 4 relations. I'm really confused about select behavior! Thanks

[tobiemh]

Hi @ehsanonline thanks for starting this discussion!

Two different things here:

1. Subsequent SELECT fields will always replace previous ones. So if you run SELECT *, <-write.in, <-write FROM article:surreal; then the last <-write field will overwrite the previous one. There is a way of getting round this. You could write something like the following:

SELECT *, <-write.in AS first, <-write AS second FROM article:surreal;

However there is a better way of doing this, which only traverses once:

SELECT *, <-(write AS first)<-(user AS second) FROM article:surreal;

2. We're looking to add a DISTINCT keyword into the graph traversal, so that it only pulls out distinct edges. Would be great to get your thoughts on this @ehsanonline !

[ehsanonline]

yes that would be great, I can't imagine v1 release, this database is already too good to be true! I really enjoyed using it, easy and powerful. thanks for your hard works. 🌹