Returned date format different in Couchbase query window vs. C# .Net query

.NET SDK
1

Hi all,
I’m working on upgrading our system from Couchbase 6 to Couchbase 8 and am running into an issue with it formatting date strings into an incompatible format.

If I run this query in the Couchbase query window

SELECT * FROM database USE KEYS “my_data”

I get something like this:

{
firstName: “John”,
lastName: “Doe”,
createdAt: “2025-12-31T20:14:35Z”
}

This is the date string that is stored in the database, and is the format that is needed. Executing this query from the Query window gives me the correct results.

However, if I execute this query in .Net:

var query = “SELECT * FROM database USE KEYS 'my_data'”;
var result = await cluster.QueryAsync<T>(query);

I get this:

{
firstName: “John”,
lastName: “Doe”,
createdAt: “2025-12-31T20:14:35+00:00”
}

It now sees that createdAt is a date and reformats it into a date string that is incompatible with our system. I never asked it to do this, and it is breaking our code in a lot of places.

This is something new with the most recent updates to the Couchbase SDK as the old system returned the dates simply as their original string.

Is there a way to configure the cluster so that it returns the string that’s stored in the database and doesn’t try to format it into a date? Or, at the very least, specify the format?

The data that our system stores is dynamic, so I can’t use anything in the query itself (like DATE_FORMAT_STR()). I need to set it at a system level.

Thanks for any help.

2

Can you provide the <T> POCO that you’re using with the query? Is the CreatedAt property a plain string property? The SDK doesn’t really do anything to mutate the data from the query, it just does JSON deserialization using the default or specified deserializer.

3

At the stage where I’m first intercepting it, it is simply an object (Newtonsoft JObject to be exact). I have yet to serialize it into anything. If the SDK doesn’t mutate the data, then I’m perplexed as to where the issue is coming from.
Previous to the new SDK, the date strings came back correctly. After the new new SDK, the results from the Couchbase query are coming back in a different format (even though they’re just strings in the database) before I’ve done anything with them.

This is all before I serialize it into a class object. That’s where the issue is coming in, since the newly formatted date string won’t serialize into a NodaTime instant. I need to convert it back to the previous datestring format for that to work.

4

Can you provide the code you’re using to parse the the JObject and extract the data? The code you’re using the run the query and iterate the results would also be helpful.

5

Sure.

It honestly isn’t much extra:

Note. In my actual code, the creation of the cluster is in the constructor, but the end result is the same.

public async Task<IEnumerable<T>> ExecuteQuery<T>(string query)
{
  var options = new ClusterOptions()
      .WithConnectionString($”couchbase://{clusterIp}")
      .WithCredentials(username: “Administrator“, password: “password”);

  var cluster = await Cluster.ConnectAsync(clusterOptions);

  var bucket = await cluster.BucketAsync(“myBucket”);
  var scope = await bucket.ScopeAsync(“\_default”);
  var collection = await scope.CollectionAsync(“\_default”);

  var result = await cluster.QueryAsync<T>(queryString);

  if (result.MetaData.Status == QueryStatus.Success)
  {
    return await results.Rows.ToListAsync();
  }

  return null;
}

The rows that come back are formatting the date string into a date format that I didn’t ask for.

The calling function

var query = “SELECT * FROM database USE KEYS 'user_credential'”;
var rows = await ExecuteQuery(query);
var credJson = rows.FirstOrDefault();

var credential = credJson?.ToObject<Credential>();

This is where the it’s crashing, since it cannot convert the new format of the date string to a NodaTime instant. I’m getting an Unexpected token parsing Instant. Expected String, got Date.

The Credential object is a simple model, with
string firstName;
string lastName;
Instant? createdAt;

Thanks for your help.

6

It’s hard to be certain from the example, but your problem may be the query itself. By default, when you do SELECT * it nests each extent underneath the extent name. This allows for structured query results when doing joins.

So this query:

SELECT * FROM database USE KEYS 'key'

Returns this JSON:

[
  {
    "database": [
      // document here in a nested array
    ]
  }
]

So the actual Credential document is nested inside an array within each row, which your ToObject call doesn’t appear to account for.

Changing the query to this will remove the nesting:

SELECT database.* FROM database USE KEYS key

The one thing I don’t understand is why any kind of upgrade would cause this problem, as that should have been the same behavior previously. But perhaps some other adjustments made accidentally broke this?

7

My mistake. Yes, there is some code to remove the nested values that I forgot in my code above.

var rows = await result.Rows.ToListAsync();
return rows[databaseName];

But yes, I’m confused what would have caused this change. The version I started with (Couchbase 4.6) returned the date string as it appears in the database. I then upgraded to Couchbase 6, which returned it correctly as well. It’s in the upgrade to Couchbase 8 that the issues appeared when the date strings started coming back formatted to a different date format.

8

Hello @justinlloyd -

Which version of the SDK are you using for each respective cluster version? Older versions used NewtonSoft and the newer versions use System.Text.Json.

Jeff

9

Our Couchbase 4.6 version uses SDK 2.6.1
Our Couchbase 6.0 version uses SDK 2.7.27
Our Couchbase 8.0 version uses SDK 3.8.1

10

@justinlloyd -

Ok, so its a migration from SDK2 to SDK3 as well…it’s more of a NodaTime and System.Text.Json issue.

You can resolve this by overriding the serializer options and using the SystemTextJsonSerializer and the NodaTime.Serialization.SystemTextJson package get the date to the correct format.

using System.Text.Json;
using Couchbase;
using Couchbase.Core.IO.Serializers;
using NodaTime;
using NodaTime.Serialization.SystemTextJson;

var cluster = await Cluster.ConnectAsync("couchbase://localhost", options =>
{
    options.WithCredentials("Administrator", "password");
    options.Serializer = SystemTextJsonSerializer.Create(new JsonSerializerOptions().ConfigureForNodaTime(DateTimeZoneProviders.Tzdb));
});

var results = await cluster.QueryAsync<Credential>("select d.* from default as d");
await foreach (var result in results.Rows)
{
    Console.WriteLine(result.createdAt);
}

public class Credential
{
    public string firstName{ get; set; }
    public string lastName{ get; set; }
    public Instant? createdAt{ get; set; }
}

The output is: 2025-12-31T20:14:35Z

11

Thank you for the explanation and getting me pointed in the right direction.
I had to modify it a bit, as this example worked for the Credential object, but there are many instances throughout our code that use JObject and this broke lots of code.
Too much to fix at this point.
So, another post lead me to this:

clusterOptions.Serializer = new DefaultSerializer(
  new JsonSerializerSettings().ConfigureForNodaTime(DateTimeZoneProviders.Tzdb),
  new JsonSerializerSettings().ConfigureForNodaTime(DateTimeZoneProviders.Tzdb)
);

This allowed it to retain the Newtonsoft.Json and work with the many instances of JObject that we have.
Seems to be working now.

1 Like
12

@justinlloyd - good to hear. Your code is the proper way to override the serializer in the SDK.

-Jeff