Complex struct nested data binding

[fsimonovskiii]

Hi,

I have a view which has the following schema:

I have a table which contains nested properties. First, for the problem I have, I want to ignore the Id and Header structs, and focus only on retrieving the data in City struct.

An example data that I would potentially retrieve is following:

{
   "NAME": "NewYork",
   "STATUS": "Ended",
   "TT1": {
      "ID": "756",
      "NAME": "Chicago",
      "TT2": {
         "ID": "10",
         "NAME": "Queens"
      },
      "TT3": {
         "ID": "169",
         "NAME": "Brooklyn"
      }
   }
}

The .NET model I have for this is as follows:

public class Cityc
{
    public CityFilter City { get; set; } = new();

    public string Name { get; set; } = string.Empty;

    public string Status { get; set; } = string.Empty;

    public TT1 TT1 { get; set; } = new();
}

public class CityFilter
{
    public TT1 TT1 { get; set; } = new();
}

public class TT1
{
    public string Id { get; set; } = string.Empty;

    public string Name { get; set; } = string.Empty;

    public TT TT2 { get; set; } = new();

    public TT TT3 { get; set; } = new();
}

public class TT
{
    public string Id { get; set; } = string.Empty;

    public string Name { get; set; } = string.Empty;
}

Executing the following pull query, seems to return the correct amount of items (if there are 4 records in kafka it creates a list of 4 items) however the fields aren't populated and it has the default values:

// creates a list with n items, however fields remain empty
var cities = await context.CreatePullQuery<Cityc>(materializedView)
    .Select(f => f.City)
    .GetManyAsync()
    .ToListAsync();

If I do a .ToQueryString() on the context above, I get the raw sql query, and it seems to be OK, looks like this:

select city from {materializedView};

Is there something additional needed when mapping structs that I am missing?

Thank you.

[tomasfabian]

Hello @fsimonovskiii , try to use the StructAttribute. https://github.com/tomasfabian/ksqlDB.RestApi.Client-DotNet/blob/27cd3600bea55294c935f3564e5ed1c8fcdb8727/docs/modelbuilder.md?plain=1#L243

[tomasfabian]

Additionally based on the provided data and classes, should the SELECT statement be modified, as City doesn’t appear to be included in the data?

var cities = await context.CreatePullQuery<Cityc>(materializedView)
    .Select(f => f.TT1)
    .GetManyAsync()
    .ToListAsync();

or even better skipped altogether?

var cities = await context.CreatePullQuery<Cityc>(materializedView)
    .GetManyAsync()
    .ToListAsync();

select * from {materializedView};

[fsimonovskiii]

Hi @tomasfabian

I tried to apply the [Struct] annotation, however still the fields remain unmapped. I will post the current set of data I am working with.

Data inserted into the Cities topic:

{
   "Header": {
      "Guid": "ee916a52-001b-47b3-aca7-4c334f6d047a",
      "TimeStampUtc": "2024-08-14T09:00:17.2089062Z"
   },
   "Id": "bg:match:11557300",
   "City": {
      "Name": "NewYork",
      "Status": "Ended",
      "TT1": {
         "ID": "bg:tournament:18783",
         "Name": "eBasketball H2H GG League Mixed",
         "TT2": {
            "ID": "bg:sport:-3795092",
            "Name": "Esports - NBA2K"
         },
         "TT3": {
            "ID": "bg:category:3795092",
            "Name": "NBA2K"
         }
      }
   }
}

Data from kafka materialized view:

{
   "NAME": "NewYork",
   "STATUS": "Ended",
   "TT1": {
      "ID": "bg:tournament:18783",
      "NAME": "eBasketball H2H GG League Mixed",
      "TT2": {
         "ID": "bg:sport:-3795092",
         "NAME": "Esports - NBA2K"
      },
      "TT3": {
         "ID": "bg:category:3795092",
         "NAME": "NBA2K"
      }
   }
}

The definition for the materialized view is as follows:

CREATE SOURCE TABLE viewcities(
  Header STRUCT<Guid VARCHAR, TimeStampUtc VARCHAR>,
  Id VARCHAR PRIMARY KEY,
  City STRUCT<Name VARCHAR,
              Status VARCHAR,
              TT1 STRUCT<ID VARCHAR, 
                         Name VARCHAR,
                         TT2 STRUCT<ID VARCHAR, Name VARCHAR>,
                         TT3 STRUCT<ID VARCHAR, Name VARCHAR>
>>)
WITH (KAFKA_TOPIC='Cities', VALUE_FORMAT='JSON');

The .NET City.cs class that I am trying to bind to, with the Struct annotation added (I tried adding it on CityC only, and different combinations between the models however it was the same outcome) looks as follows:

[Struct]
public class CityC
{
    public string Name { get; set; } = string.Empty;
    public string Status { get; set; } = string.Empty;
    public TT1 TT1 { get; set; } = new();
    public CityFilter City = new(); // use this field as filter in the .Where linq method, for the ID
}

[Struct]
public class TT1
{
    public string ID { get; set; } = string.Empty;
    public string Name { get; set; } = string.Empty;
    public CityDto TT2 { get; set; } = new();
    public CityDto TT3 { get; set; } = new();
}

[Struct]
public class CityDto
{
    public string ID { get; set; } = string.Empty;
    public string Name { get; set; } = string.Empty;
}

public class CityFilter
{
    public TT1Filter TT1 { get; set; } = new();
}

public class TT1Filter
{
    public string ID { get; set; } = string.Empty;
}

The ksqldb part of the code is as follows:

var data = await context.CreatePullQuery<CityC>("viewcities")
    .Where(c => c.City.TT1.ID == "bg:tournament:18783")
    .GetManyAsync()
    .ToListAsync();

Binding still does not appear to be working, any help is appreciated, thanks.

[tomasfabian]

This should work based on the data provided above:

public class CityRow
{
  public string Id { get; set; } = string.Empty;
  public City City { get; set; } = null!;
}

[Struct]
public class City
{
  public string Name { get; set; } = string.Empty;
  public string Status { get; set; } = string.Empty;
  public TT1 TT1 { get; set; } = new();
}

var data = await context.CreatePullQuery<CityRow>("viewcities")
      .Where(c => c.City.TT1.ID == "bg:tournament:18783")
      .GetManyAsync()
      .ToListAsync();

[fsimonovskiii]

Thank you @tomasfabian, the missing key Id field fixed the issue!