Cannot Get JSON as String with QueryAsync

.NET SDK
1

Hello,

I am trying to get my Couchbase query result as string. Because I don’t have a type information at compile time. Our documents can all have different types. I want one generic query method to return to me the document content.

What I am using is this:

[HttpPost("query")]
        public async Task<IActionResult> Query([FromBody] string query)
        {
            try
            {
                var bucket = await _packagesBucketProvider
                    .GetBucketAsync()
                    .ConfigureAwait(false);
                var cluster = bucket.Cluster;
                var queryResult = await cluster.QueryAsync<string>(query);
                var rows = await queryResult.Rows.ToListAsync();
                return Ok(rows);
            }
            catch (Exception e)
            {
                return StatusCode(500, e.Message);
            }
        }

When I call this action with any query I get this result: Error reading string. Unexpected token: StartObject. Path '', line 6, position 1.

My Couchbase configuration is this:

services.AddCouchbase(client =>
    {
        client.WithConnectionString($"couchbase://{MY_COUCHBASE_SERVERS}");
        client.EnableTls = false;
        client.UserName = "MY_USERNAME";
        client.Password = "MY_PASSWORD";
        client.KvTimeout = TimeSpan.FromMilliseconds(90000);
        client.MaxHttpConnections = 20;
    })
    .AddCouchbaseBucket<IMyBucketProvider>("MyBucket");

What I want is such a simple to thing (get document content as string) yet I cannot do this. Using object or dynamic doesn’t work I expect it.

Thanks,
Dogac

2

@dogac_akyildiz

Unfortunately, executing N1QL queries and getting the raw JSON data isn’t currently supported. The data is always deserialized by the query client in the SDK.

You have four options that I can think of:

  1. I know you said that using dynamic didn’t work as you expect, but it should get you what you need. Let me know if you have any questions and I’m happy to help. Note: the most likely problem is how .NET is serializing the response. Since .NET Core 3.1, by default it uses System.Text.Json to serialize IActionResult objects. To serialize the dynamic results from the Couchbase SDK made with the default serializer they must be serialized with Newtonsoft.Json. You could switch your app to use Newtonsoft for MVC and it would probably fix the problem.
  2. Alternatively, if you’re using the default serializer, you can use dynamic as a stepping stone. After you get the dynamic row, calling JsonConvert.SerializeObject(row) or row.ToString() should get you the raw JSON for that row. That said, it’s pretty inefficient since it is deserializing and reserializing the data. It may also get different whitespace formatting.
  3. You can always use the REST API against query nodes yourself to execute the queries directly, which would give the greatest flexibility. However, doing this in a robust manner that handles query nodes being added and removed from the cluster is non-trivial.
  4. Change your query to use SELECT RAW META().id ... and then await cluster.QueryAsync<string>(...). This query will get the list of document keys rather than the actual document. Then you may use var result = await collection.GetAsync(key, options => options.Transcoder(new RawJsonTranscoder()) and result.ContentAs<string>() to get the raw JSON for each document.
3

Could you help understand and explain why this doesn’t work as expected?

4

Hello @btburnett3

Thanks for your detailed answer. It is very helpful.

We switched to a combination of method 1 and method 2 currently, and make it work (return raw JSON).

And I will probably consider method 4 too, it sounds very plausible.

Thanks,
Dogac

1 Like
5

Hello @jmorris

I was trying to get the raw JSON data for the query result. I want the query to return directly the result that I see in Couchbase UI. I don’t need any intermediate types. As my documents can have different types.

We switched to one the methods @btburnett3 suggested, and we can optimize this method in the future too.

Thanks,
Dogac

6

For anyone that faces this problem, it may be helpful, what I ended up doing is this:

[HttpPost("query")]
public async Task<IActionResult> Query([FromBody] string query)
{
    try
    {
        var bucket = await _myBucketProvider
            .GetBucketAsync()
            .ConfigureAwait(false);
        var cluster = bucket.Cluster;
        var queryResult = await cluster.QueryAsync<dynamic>(query);
        var rows = await queryResult
            .Rows
            .ToListAsync();

        return Content(JsonConvert.SerializeObject(rows), "application/json");
    }
    catch (Exception e)
    {
        return StatusCode(500, e.Message);
    }
}

And it works as expected (returns raw JSON data).

This is the method 1 of @btburnett3 suggestions.

Thanks