Unable to get Keys as expected while executing N1QL query

Hi Team,

I have data in Couchbase server with Binary Documents.

I am using N1Ql to fetch the documents using query
select * from VariableData b WHERE Meta(b).id LIKE ‘cn_billingphonenumber123_hashed%’,
where I am trying to fetch all documents starting with “cn_billingphonenumber123_hashed”.

I am getting the results as

"results": [
    {
    `    "b": "<binary (139 b)>"`
    },
    {
     `   "b": "<binary (139 b)>"`
    }
],

However, my concern is, I am not able to see key names coming as expected in results.
My key while storing is “
cn_billingphonenumber123_hashed|1004901933|10.12.121.140”

I have to store the results in Map and return, but, not knowing how to get keys correctly.
Below is my code in java.

      N1qlQuery query = null;
      long timeout = 2000l;
      String layer_name_prefix = variableName + "%";
      StringBuffer slcStmnt = new StringBuffer(
          "select * from VariableData b WHERE META(b).id LIKE '" + layer_name_prefix + "'");
      query = N1qlQuery.simple(slcStmnt.toString());
      N1qlQueryResult result = dataSource.instanceBucket.query(query, timeout, TimeUnit.SECONDS);
      if (!result.finalSuccess()) {
        System.err.println("Query error: " + result.errors());
      }
      List<Map<String, Object>> content = new ArrayList<Map<String, Object>>();
      for (N1qlQueryRow row2 : result) {
        content.add(row2.value().toMap());       
      }
 System.out.println(content);

Results printed for content

{b=<binary (139 b)>}

Could you help me on this.

I’ve read this through a few times, and the tough thing in assisting here is that it’s not clear what the data is. My apologies if I’m missing it.

Is it possible, perhaps, to just post a small program that demonstrates the issue? What’s the “Binary Document” you’re working with and how are you trying to query it?

Here is what I am trying to insert

I have a java object which holds list of values against a key. I am converting that object to a BinaryDocument.

The Key is combination of few params(Dataname+value1+value2…etc) and value is BinaryDocument which is a List of records.

DataInstance dataInstance = new DataInstance();
String dataKey = “cn_billingphonenumber123_hashed|1004901933|10.12.121.16”;
dataInstance.setName(dataKey);
String[] dataInstanceValueFields = {“customer_id”, “98571890”, “event”, “xyz”,
“timestamp”, currentTimestamp.minusMillis(40000).toString(format), “Name”, “TestName”,
“age”,“25”};
DataInstanceValue dataInstanceValue =
new DataInstanceValue(dataInstanceValueFields);
dataInstance.setValue(dataInstanceValue);
BinaryDocument newBinDoc = BinaryDocument.create(dataKey, expiry,Unpooled.copiedBuffer(dataInstanceValue), documentCas);
dataSource.instanceBucket.insert(newBinDoc);

I need to fetch all BinaryDocuments based on common word available in Key.
Ex: here cn_billingphonenumber123_hashed is the field of the Key that I use to fetch the records as said previously.

I am not filtering anything inside the document. I am just applying filter on Keys and getting all documents.

Ah, I think I understand a bit better. I was hoping to be able to edit the code to give you something, but I don’t have the DataInstance class you reference.

Assuming query returns what you need, I think you’ll want to use the RawQueryExecutor. This will give you the results returned by the query service back as a simple java.lang.String object. Have a look at New Raw Query Result for the Java SDK for some examples.

OK.

When I am executing
select * from VariableData b WHERE Meta(b).id LIKE ‘cn_billingphonenumber123_hashed%’ query in N1QL command line,

My results are getting displayed as

“results”: [
{
"b": "<binary (139 b)>"
},
{
"b": "<binary (139 b)>"
}
]

How can I get keys in the results instead of b
something like :
“results”: [
{
"cn_billingphonenumber123_hashed|1004901933|10.12.121.15": "<binary (139 b)>"
},
{
"cn_billingphonenumber123_hashed|1004901933|10.12.121.16": "<binary (139 b)>"
}
]

you should get doc’s key by META().id, for example

select META(b).id,* from VariableData b WHERE Meta(b).id LIKE 'cn_billingphonenumber123_hashed%'
1 Like

Thank you @atom_yang and @ingenthr for your help.