Couchbase return null value after save and read document using n1ql

I insert a document in couchbase using repository.save() and after that, I make a query to find duplicates on another document.

query is:

SELECT ARRAY_AGG(i.serialnumber) serialNumbers
FROM default tempItem
UNNEST items i
WHERE tempItem.class = "com.inventory.model.item.TempItem"
    AND META(tempItem).id = '4390dd9e-e392-4432-939f-ebf046570086'
    and i.serialnumber in (select raw serialnumber from default where class = 'com.inventory.model.item.Item'
    AND status != 'DELETED' and serialnumber is not missing)

the result of the query is:

[
  {
    "serialNumbers": [
      "9121945901",
      "9121955901",
      "9211965901"
    ]
  }
]

The document that saved is like below:

[
  {
    "tempItem": {
      "class": "com.inventory.model.item.TempItem",
      "items": [
        {
          "categoryId": "67aaca7b-90b1-43e4-a6c6-0e9567bf283e",
          "clientIds": [
            "919d0ca7-c8d4-4283-8b0a-b6f2a7b39753"
          ],
          "description": "bla bla",
          "initial": 1,
          "productId": "db5c81c4-0fec-407e-8703-6f5fb69a070c",
          "serialnumber": "9121945901",
          "simType": "PREPAID",
          "status": "ACTIVE",
          "stock": 1,
          "title": "bla bla"
        }
      ]
    }
  }
]

and another document to check is :

{
  "categoryId": "67aaca7b-90b1-43e4-a6c6-0e9567bf283e",
  "class": "com.inventory.model.item.Item",
  "clientIds": [
    "919d0ca7-c8d4-4283-8b0a-b6f2a7b39753"
  ],
  "createdts": 1601801989176,
  "creator": "919d0ca7-c8d4-4283-8b0a-b6f2a7b39753",
  "description": "bla bla",
  "initial": 1,
  "prefix1": "912",
  "prefix2": "194",
  "productId": "db5c81c4-0fec-407e-8703-6f5fb69a070c",
  "serialnumber": "9121945901",
  "simType": "PREPAID",
  "status": "ACTIVE",
  "stock": 1,
  "title": "bla bla"
}

in spring boot when I run the query immediately after seve the document its return null result and if I make some milliseconds sleep after save and before the run query returns the value

what is that problem? can anybody help this issue?

You need to use scan_consistency as desribed by @matthew.groves https://stackoverflow.com/questions/64207079/couchbase-return-null-value-after-save-and-read-document-using-n1ql/64209155#64209155

As the document is not reached to indexer, The query produced 0 documents. Due to aggregation without group by must produce single result document. So result will be NULL.

Alternative is USE KEYS instead of index scan as you already know document key

SELECT ARRAY_AGG(i.serialnumber) serialNumbers
FROM default tempItem USE KEYS '4390dd9e-e392-4432-939f-ebf046570086'
UNNEST items i
WHERE tempItem.class = "com.inventory.model.item.TempItem"
    and i.serialnumber in (select raw serialnumber from default where class = 'com.inventory.model.item.Item'
    AND status != 'DELETED' and serialnumber is not missing);
1 Like