N1QL based query of nested nested objects

n1ql

#1

Hello everyone, I’m working on a professional social network application using couchbase server 4.1 with spring data couchbase 2.2.4 in a spring boot application using java 1.8.
I want to replace the next stream which searches a LikeEntity with specific company in the database by a N1QL based query which searches a user with the same previous likeEntity unnested:
Here is the service containing the stream to replace by the query:

@Service
class CompanyServiceImpl implements CompanyService{

        @Override
        public void addCompanyToLike(UserEntity user, Company company){
		      int incrementValue=1;
              LikeEntity existingLike=user.getLikeEntities()
                                          .stream()
						                  .filter(le->le.getCompany().getName().equals(company.getName()))
	         							  .findFirst();
              //rest of process
        }
     }   

Here is the different java beans you will need to look at:
UserEntity class:

@Document
    @ViewIndexed(designDoc = "user")
    class UserEntity implements Serializable{
        @Field private String id;
        @Reference
        private List<LikeEntity> likeEntities=new ArrayList<LikeEntity>();
        
        //Other attributes plus getters and setters:
    }   

LikeEntity class:

@Document
    class LikeEntity implements serializable{
        
        @Reference
        private Company company;
        
        @Reference
        private Job job;

        // other attributes plus getters and setters
    }   

As you see above, the LikeEntity class may contain any object liked by the user, it can be a company, a job or another object. Also the LikeEntity is stored only inside a user document as element of user’s list of likes and not independately in the database.It’s my choice of modelization because the LikeEntity won’t by used in other java beans of my application

Company:

 @Document
    @ViewIndexed(designDoc = "company")
    class Company implements Serializable{
      @Field private  String id;
      @Field private String name;
      //Other attributes plus getters and setters 
    }  

N.B: I’ve tried the next query inside UserRepository but it didn’t work:

UserRepository:

     @Repository
        @N1qlPrimaryIndexed
        @N1qlSecondaryIndexed(indexName = "user")
        public interface UserRepository extends CouchbaseRepository<UserEntity, String> {
            @Query("SELECT b.*, likeEntity FROM #{#n1ql.bucket} AS b UNNEST b.likeEntities AS likeEntity WHERE b.id=$1 AND likeEntity.company.name=$2")
            UserEntity findByLikedCompanyName(String idUser , String companyName);
        }      

I’m looking for your answers, and thank you so much in advance.


#2

Hi there,

Sorry for the long delay!

What exactly is the expected return type of this query? The easiest way is to use something like ARRAY_CONTAINS if you need to return the user https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/arrayfun.html#story-h2-5

If you need to return a list of likes, it will require some extra work:

SELECT c.* FROM tutorial t UNNEST t.children c WHERE c.age > 10

String query = "Select c.* from myBucket b UNNEST b.likeEntities c where b.id = 'someId' and c.company.id = 'aCompanyId'  "

N1qlParams params = N1qlParams.build().consistency(ScanConsistency.NOT_BOUNDED).adhoc(true);
ParameterizedN1qlQuery query = N1qlQuery.parameterized(queryString, JsonObject.create(), params);
return userRepository.getCouchbaseOperations().findByN1QLProjection(query, LikeEntity.class);

There some other possibilities also, but those two is how I usually do it.


#3

“deniswsrosa”, first of all I want to thank you a lot for your replication, second of all, I want to inform you that I have visited the link which you include in your resposne, it’s helpful, but it stills not responding to my need.
I want to have as type of the query return a UserEntity with the LikeEntity requested but flattened. For more clarification, here is an exmaple of the output I’m looking for:

  • Let’s suppose there is in the database the next user who I want to retreive using the N1QL query based on the name of the liked company ‘couchbase’:

       {
              'id':'123456',
              'email':'test@test.com',
              'username':'test1',
              'likeEntities':[
                          {
                                    'likeId':'01010',
                                    'company':{
                                             'name':''couchBase'
                                    }
                           },
                           {
                                    'likeId':'02222',
                                    'company':{
                                               'name':''MongoDB'
                                    }
                           },
                           {
                                     'likeId':'033333',
                                     'job':{
                                              'label':''Full stack JEE developer'
                                     }
                            },                   
                   ]
    
       //other attributes
       }
    
  • The output that I’m looking for is:

      {
                    'id':'123456',
                    'email':'test@test.com',
                    'username':'test1',
                    'likeEntity':
                                {
                                          'likeId':'01010',
                                          'company':{
                                                   'name':''couchBase'
                                          }
                                 }               
                         //other attributes
                        //As you can see I want to replace the list of likeEntities by the only object LikeEntity
                       //whitch contains the liked company 'couchbase'
          }
    
  • And if the user doesn’t exit in the database, I want to have a null Object:

Finally I want to thank you again for your reply, it means a lot for me.


#4
SELET d.*, du.* FROM default AS d UNNEST d.likeEntities AS du 
WHERE .......;

#5

“vsr1” Thanks for your answer, your query is the first one that I’ve tried before posting this topic in couchbase forum, but it didn’t work for me, I don’t know why, and here it is, as showen in the userRepository above, in the first post.

@Query("SELECT b.*, likeEntity FROM #{#n1ql.bucket} AS b UNNEST b.likeEntities AS likeEntity WHERE b.id=$1 AND likeEntity.company.name=$2") 
UserEntity findByLikedCompanyName(String idUser , String companyName); }

#6

Post the sample document


#7

“vsr1”, thank you so much for your quick reaction, I was wondering which sample document you want me to post. If you are talking about the userEntity document, I’ve just posted, as a replication to the first answer posted by “deniswsrosa”, an example of the output I’m looking for using a sample of userEntity document.


#8

The following is output from web console. Make sure it is working right afterwords convert into correct client syntax.

INSERT INTO default VALUES(“kk01”, { ‘id’:‘123456’, ‘email’:‘test@test.com’, ‘username’:‘test1’, ‘likeEntities’:[ { ‘likeId’:‘01010’, ‘company’:{ ‘name’:‘couchBase’ } }, { ‘likeId’:‘02222’, ‘company’:{ ‘name’:‘MongoDB’ } }, { ‘likeId’:‘033333’, ‘job’:{ ‘label’:‘Full stack JEE developer’ } }] });

SELECT d.*, du.* FROM default AS d USE KEYS "kk01" UNNEST d.likeEntities AS du WHERE d.id = "123456" AND du.company.name = "couchBase";

SELECT d.*, du.* FROM default AS d USE KEYS "kk01" UNNEST d.likeEntities AS du WHERE d.id = "123456" AND du.company.name = "couchBase";
{
    "requestID": "95e3ef00-8b7c-453f-9bfa-9a4bc8b6702f",
    "signature": {
        "*": "*"
    },
    "results": [
        {
            "company": {
                "name": "couchBase"
            },
            "email": "test@test.com",
            "id": "123456",
            "likeEntities": [
                {
                    "company": {
                        "name": "couchBase"
                    },
                    "likeId": "01010"
                },
                {
                    "company": {
                        "name": "MongoDB"
                    },
                    "likeId": "02222"
                },
                {
                    "job": {
                        "label": "Full stack JEE developer"
                    },
                    "likeId": "033333"
                }
            ],
            "likeId": "01010",
            "username": "test1"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "4.428515ms",
        "executionTime": "4.309615ms",
        "resultCount": 1,
        "resultSize": 796
    }
}

#9
INSERT INTO default VALUES("kk01", { 'id':'123456', 'email':'test@test.com', 'username':'test1', 'likeEntities':[ { 'likeId':'01010', 'company':{ 'name':'couchBase' } }, { 'likeId':'02222', 'company':{ 'name':'MongoDB' } }, { 'likeId':'033333', 'job':{ 'label':'Full stack JEE developer' } }] });

SELECT d.*,
       ARRAY v FOR v IN d.likeEntities WHEN v.company.name = "couchBase" END AS likeEntities
FROM default AS d USE KEYS "kk01"
WHERE d.id = "123456" AND ANY v IN d.likeEntities SATISFIES v.company.name = "couchBase" END;

{
    "requestID": "be398b0a-3daf-41e9-a0cb-9eec64fdf6af",
    "signature": {
        "*": "*",
        "likeEntities": "array"
    },
    "results": [
        {
            "email": "test@test.com",
            "id": "123456",
            "likeEntities": [
                {
                    "company": {
                        "name": "couchBase"
                    },
                    "likeId": "01010"
                }
            ],
            "username": "test1"
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "2.901676ms",
        "executionTime": "2.773642ms",
        "resultCount": 1,
        "resultSize": 328
    }
}

#10

‘vsr1’, thank you, always, for your answers. On the one hand, the result of your query doesn’t correspond to the result I’m looking for. I would like to know if we can have a likeEntity flattened containing the company which name is ‘couchbase’, rather than this last one flattened as showen in your result.
On the other hand, I would like to know what does " USE KEYS kk01", in your query mean?
And thanks a lot in advance.


#11

‘vsr1’, I was wondering if you can show me the result of your last query, I didn’t understand it.
Thank you.


#12

USE KEYS just example to work only this document, In your case you can remove it (so it uses indexscan based on predicates) and change bucket and conditions you want.

ANY v IN d.likeEntities SATISFIES v.company.name = "couchBase" END;

Above predicates check if any object in the array has matching condition. If there is one document is selected.

ARRAY v FOR v IN d.likeEntities WHEN v.company.name = "couchBase" END AS likeEntities

Above expression projection iterates through array constructs new array with matching entries only.


#13

‘vsr1’, your second query gives the result I’m looking for, but the only thing I didn’t understand about it is the expression "USE KEYS “kk01”. I think I made a mistake by including the id of the user’s document inside it as property, it’s not the case in my couchBase server, the id is outside the document, so I will change the output sample, and I would like to know if the your query will work in this case.
Thank you so much for your help.


#14

Without use keys and matching by username

SELECT d.*,
       ARRAY v FOR v IN d.likeEntities WHEN v.company.name = "couchBase" END AS likeEntities
FROM default AS d
WHERE d.username = "test1" AND ANY v IN d.likeEntities SATISFIES v.company.name = "couchBase" END;

id is outside document means it is document key, Your original query has b.id = $1, if you already know the document key in that case you can try this

  SELECT d.*,
           ARRAY v FOR v IN d.likeEntities WHEN v.company.name = "couchBase" END AS likeEntities
    FROM default AS d USE KEYS $1
    WHERE ANY v IN d.likeEntities SATISFIES v.company.name = "couchBase" END;

#15

‘vsr1’, I have just read your last replication, now I understand, completly, your query, and my output sample was correct, there is no mistake in it.
Last but not least, I was wondering if you can help me writting your query using spring Expression Language so as to have a N1QL based query. Anyway, you helped me a lot, thank you so much.


#16

I have limited expertise in that area. cc @deniswsrosa


#17

Thank you so much, you helped me a lot, you can’t imagine how your answers were very helpful for me, and because of you I learnt new informations. All my respects great competency.


#18

I am glad able to help you. Checkout the N1QL guides by @keshav_m


#19

Thank you so much for the reference, it’s very helpful.