Getting empty documents

Here is my full bucket:

select * from `silver-spoon`
[
  {
    "silver-spoon": 12
  },
  {
    "silver-spoon": {
      "history": {
        "action": "CREATE",
        "data": {
          "subscriptionFeature": {
            "featureCategory": "string",
            "featureCode": "string",
            "featureDescription": "string",
            "featureName": "string"
          }
        },
        "roles": [
          "DEVELOPER"
        ],
        "time": "Mar 19, 2017 1:25:51 AM",
        "type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
        "user": "Abhideep"
      }
    }
  },
  {
    "silver-spoon": {
      "history": {
        "action": "CREATE",
        "data": {
          "subscriptionFeature": {
            "featureCategory": "string",
            "featureCode": "string9",
            "featureDescription": "string",
            "featureName": "string"
          }
        },
        "roles": [
          "DEVELOPER"
        ],
        "time": "Mar 28, 2017 1:52:01 AM",
        "type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
        "user": "Abhideep"
      }
    }
  },
  {
    "silver-spoon": {
      "history": {
        "action": "CREATE",
        "data": {
          "subscriptionFeature": {
            "featureCategory": "string",
            "featureCode": "stringt",
            "featureDescription": "string",
            "featureName": "string"
          }
        },
        "roles": [
          "DEVELOPER"
        ],
        "time": "Mar 28, 2017 2:07:51 AM",
        "type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
        "user": "Abhideep"
      }
    }
  },
  {
    "silver-spoon": {
      "history": {
        "action": "CREATE",
        "data": {
          "subscriptionFeature": {
            "featureCategory": "string",
            "featureCode": "stringm",
            "featureDescription": "string",
            "featureName": "string",
            "id": "16"
          }
        },
        "roles": [
          "DEVELOPER"
        ],
        "time": "Mar 28, 2017 2:23:43 AM",
        "type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
        "user": "Abhideep"
      }
    }
  },
  {
    "silver-spoon": {
      "history": {
        "action": "DELETE",
        "data": {
          "subscriptionFeature": {
            "featureCategory": "string",
            "featureCode": "string",
            "featureDescription": "string",
            "featureName": "string"
          }
        },
        "roles": [
          "DEVELOPER"
        ],
        "time": "Mar 19, 2017 3:36:53 AM",
        "type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
        "user": "Abhideep"
      }
    }
  },
  {
    "silver-spoon": {
      "history": {
        "action": "CREATE",
        "data": {
          "subscriptionFeature": {
            "featureCategory": "string",
            "featureCode": "string",
            "featureDescription": "string",
            "featureName": "string"
          }
        },
        "roles": [
          "DEVELOPER"
        ],
        "time": "Mar 19, 2017 4:48:24 PM",
        "type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
        "user": "Abhideep"
      }
    }
  },
  {
    "silver-spoon": {
      "history": {
        "action": "CREATE",
        "data": {
          "subscriptionFeature": {
            "featureCategory": "string",
            "featureCode": "string",
            "featureDescription": "string",
            "featureName": "string"
          }
        },
        "roles": [
          "DEVELOPER"
        ],
        "time": "Mar 19, 2017 6:16:50 PM",
        "type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
        "user": "Abhideep"
      }
    }
  },
  {
    "silver-spoon": {
      "history": {
        "action": "CREATE",
        "data": {
          "subscriptionFeature": {
            "featureCategory": "string",
            "featureCode": "string3",
            "featureDescription": "string",
            "featureName": "string"
          }
        },
        "roles": [
          "DEVELOPER"
        ],
        "time": "Mar 28, 2017 1:00:27 AM",
        "type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
        "user": "Abhideep"
      }
    }
  },
  {
    "silver-spoon": {
      "history": {
        "action": "CREATE",
        "data": {
          "subscriptionFeature": {
            "featureCategory": "string",
            "featureCode": "string5",
            "featureDescription": "string",
            "featureName": "string"
          }
        },
        "roles": [
          "DEVELOPER"
        ],
        "time": "Mar 28, 2017 1:24:17 AM",
        "type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
        "user": "Abhideep"
      }
    }
  },
  {
    "silver-spoon": {
      "history": {
        "action": "CREATE",
        "data": {
          "subscriptionFeature": {
            "featureCategory": "string",
            "featureCode": "string7",
            "featureDescription": "string",
            "featureName": "string"
          }
        },
        "roles": [
          "DEVELOPER"
        ],
        "time": "Mar 28, 2017 1:45:42 AM",
        "type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
        "user": "Abhideep"
      }
    }
  },
  {
    "silver-spoon": {
      "history": {
        "action": "CREATE",
        "data": {
          "subscriptionFeature": {
            "featureCategory": "string",
            "featureCode": "string8",
            "featureDescription": "string",
            "featureName": "string"
          }
        },
        "roles": [
          "DEVELOPER"
        ],
        "time": "Mar 28, 2017 1:47:57 AM",
        "type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
        "user": "Abhideep"
      }
    }
  },
  {
    "silver-spoon": 16
  },
  {
    "silver-spoon": {
      "subscriptionFeature": {
        "featureCategory": "string",
        "featureCode": "string3",
        "featureDescription": "string",
        "featureName": "string"
      }
    }
  },
  {
    "silver-spoon": {
      "subscriptionFeature": {
        "featureCategory": "string",
        "featureCode": "string5",
        "featureDescription": "string",
        "featureName": "string"
      }
    }
  },
  {
    "silver-spoon": {
      "subscriptionFeature": {
        "featureCategory": "string",
        "featureCode": "string7",
        "featureDescription": "string",
        "featureName": "string"
      }
    }
  },
  {
    "silver-spoon": {
      "subscriptionFeature": {
        "featureCategory": "string",
        "featureCode": "string8",
        "featureDescription": "string",
        "featureName": "string"
      }
    }
  },
  {
    "silver-spoon": {
      "subscriptionFeature": {
        "featureCategory": "string",
        "featureCode": "string9",
        "featureDescription": "string",
        "featureName": "string"
      }
    }
  },
  {
    "silver-spoon": {
      "subscriptionFeature": {
        "featureCategory": "string",
        "featureCode": "stringt",
        "featureDescription": "string",
        "featureName": "string"
      }
    }
  },
  {
    "silver-spoon": {
      "subscriptionFeature": {
        "featureCategory": "string",
        "featureCode": "stringm",
        "featureDescription": "string",
        "featureName": "string",
        "id": "16"
      }
    }
  },
  {
    "silver-spoon": {
      "subscriptionFeature": {
        "featureCategory": "string",
        "featureCode": "string",
        "featureDescription": "string",
        "featureName": "string"
      }
    }
  },
  {
    "silver-spoon": {
      "subscriptionFeature": {
        "featureCategory": "string",
        "featureCode": "string",
        "featureDescription": "string",
        "featureName": "string"
      }
    }
  },
  {
    "silver-spoon": {
      "subscriptionFeature": {
        "featureCategory": "string",
        "featureCode": "string2",
        "featureDescription": "string",
        "featureName": "string"
      }
    }
  },
  {
    "silver-spoon": {
      "cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-6"
    }
  },
  {
    "silver-spoon": {
      "cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-9"
    }
  },
  {
    "silver-spoon": {
      "cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-10"
    }
  },
  {
    "silver-spoon": {
      "cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-11"
    }
  },
  {
    "silver-spoon": {
      "cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-12"
    }
  },
  {
    "silver-spoon": {
      "cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-13"
    }
  },
  {
    "silver-spoon": {
      "cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-14"
    }
  },
  {
    "silver-spoon": {
      "cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-16"
    }
  },
  {
    "silver-spoon": {
      "cbLookupDocumentkey": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity-15"
    }
  }
]

This has 32 objects. Now when I execute following:

select subscriptionFeature from `silver-spoon` 

I get this (Notice Result Count as 32 here) :

[
  {},
  {},
  {},
  {},
  {},
  {},
  {},
  {},
  {},
  {},
  {},
  {},
  {},
  {
    "subscriptionFeature": {
      "featureCategory": "string",
      "featureCode": "string3",
      "featureDescription": "string",
      "featureName": "string"
    }
  },
  {
    "subscriptionFeature": {
      "featureCategory": "string",
      "featureCode": "string5",
      "featureDescription": "string",
      "featureName": "string"
    }
  },
  {
    "subscriptionFeature": {
      "featureCategory": "string",
      "featureCode": "string7",
      "featureDescription": "string",
      "featureName": "string"
    }
  },
  {
    "subscriptionFeature": {
      "featureCategory": "string",
      "featureCode": "string8",
      "featureDescription": "string",
      "featureName": "string"
    }
  },
  {
    "subscriptionFeature": {
      "featureCategory": "string",
      "featureCode": "string9",
      "featureDescription": "string",
      "featureName": "string"
    }
  },
  {
    "subscriptionFeature": {
      "featureCategory": "string",
      "featureCode": "stringt",
      "featureDescription": "string",
      "featureName": "string"
    }
  },
  {
    "subscriptionFeature": {
      "featureCategory": "string",
      "featureCode": "stringm",
      "featureDescription": "string",
      "featureName": "string",
      "id": "16"
    }
  },
  {
    "subscriptionFeature": {
      "featureCategory": "string",
      "featureCode": "string",
      "featureDescription": "string",
      "featureName": "string"
    }
  },
  {
    "subscriptionFeature": {
      "featureCategory": "string",
      "featureCode": "string",
      "featureDescription": "string",
      "featureName": "string"
    }
  },
  {
    "subscriptionFeature": {
      "featureCategory": "string",
      "featureCode": "string2",
      "featureDescription": "string",
      "featureName": "string"
    }
  },
  {},
  {},
  {},
  {},
  {},
  {},
  {},
  {},
  {}
]

While if I get the count, I get 10.

Count is right. But if I add limit:

select subscriptionFeature from `silver-spoon` limit 5

I get this:

[
  {},
  {},
  {},
  {},
  {}
]

MY question is why its is not giving the right documents and instead giving me blank document. Whats wrong ??

you can query data with META() Info to check what’s wrong with your {} data by

SELECT META() AS META,silver-spoon.* FROM silver-spoon LIMIT 5;

Your query

select subscriptionFeature from `silver-spoon`

does not remove objects, where subscriptionFeature is on the third level deep in the document. Like this:

  {
    "silver-spoon": {
      "history": {
        "action": "CREATE",
        "data": {
          "subscriptionFeature": {
            "featureCategory": "string",
            "featureCode": "string8",
            "featureDescription": "string",
            "featureName": "string"
          }
        },
        "roles": [
          "DEVELOPER"
        ],
        "time": "Mar 28, 2017 1:47:57 AM",
        "type": "com.fastserve.aawp.aawp.cb.entity.SubscriptionFeatureEntity",
        "user": "Abhideep"
      }
    }
  }

This is why you see the empty objects in the second query. When you have added limit, you just take first records, which happened all empty. Try

select subscriptionFeature from `silver-spoon` LIMIT 14

And you will see first non-empty result.

 select subscriptionFeature from `silver-spoon` where  subscriptionFeature IS NOT MISSING;
1 Like

Your N1QL has syntax error. Thanks for replying though.

I do not think in that given query, I have anyway mentioned to look at the third level. I am not doing text search here that it should look every where. When I say subscriptionFeature, it is expected to look at the top level object, not into the hierarchy of the object.

People opinion please here.

is not missing or is not null is working. But my question is, in this way of querying, are we scanning all the documents in the bucket. My whole concern is just to scan the objects which are stored as subscriptionFeature and query should not go through the object which do not start with top level object as subscriptionFeature

@avsej , @atom_yang opinion please.

CRETE INDEX ix1 ON `silver-spoon` (subscriptionFeature);
SELECT subscriptionFeature FROM `silver-spoon` WHERE subscriptionFeature IS NOT MISSING;

If subscriptionFeature is object you can use type field that specify the type of document and index on type and query based on type.

Above query uses ix1 index. ix1 will not have entries where subscriptionFeature is MISSING.

The syntax error is due to special character in your bucket name. Use back ticks.

@vsr1 Thanks for your response. I guess I am doing fundamentally something wrong. Let me explain my intent first. The second query you wrote works even without creating the ix1 index.

I am(/will be) storing various type of documents in the bucket. Now when I need to list a particular type of documents, I do not want to do something like where type=“employee” . In my opinion, this will go and scan all the document and will try to fulfill the where clause. This would be problem when my bucket will have thousands of records and at that time, scanning every document to fulfill that where clause will hit the performance of the query. Rather, I want the query only to pick the documents which are stored as with root elements as employee , in this case subscriptionFeature. So even if I have thousands of object it will only pick up the document with root elements as employee.

If you have primary index it scans all documents. In this case we are creating secondary index on type and it only fetch the required documents based on WHERE clause.

You should look sample bucket `travel-sample`. It has 5 different type of documents. each document has type field specifies type of document.

CREATET INDEX idx_type `travel-sample`(type);
SELECT * FROM `travel-sample` WHERE type = "hotel";

Above query uses idx_type and does IndexScan on type=“hotel” and fetch required documents.

You can find more details


https://developer.couchbase.com/documentation/server/4.5/travel-app/travel-app-data-model.html

You can also prefix each document KEY in the bucket with document type. ex. “EMPLOYEE::xxxxxx”

CRETE INDEX primary ON `silver-spoon`;
SELECT * FROM `silver-spoon` WHERE META().id LIKE "EMPLOYEE::%";