Aggregate into ARRAY<STRUCT<...>>

ksqlDB
1

I am attempting to aggregate data into a structure similar to

-- Starting
CustomerId INTEGER,
ContactId STRING, 
Name STRING, 
Phone STRING

-- Desired Result
CustomerId INTEGER,
Contacts ARRAY< STRUCT< ContactId STRING, Name STRING, Phone STRING > >

When using TOPK to GROUP BY I am left with a struct of sort_col, col0, col1, etc., so I am trying to TRANSFORM that result, but it is unable to find the columns defined by TOPK:

CREATE TABLE TEMP_CONTACTS
AS SELECT
  CustomerId,
  TOPK(
    ContactId, 
    Name,
    Phone,
    10) AS Contacts
FROM CONTACTS
GROUP BY CustomerId
EMIT CHANGES;

SELECT
  CustomerId,
  TRANSFORM(Contacts, (contact) => STRUCT(ContactId := contact->sort_col)) AS Contacts
FROM TEMP_CONTACTS EMIT CHANGES;
-- > Could not find field 'SORT_COL' in 'CONTACT'.

Is there a better way to accomplish this aggregation, or something I am missing with the transformation? I had tried to convert it to an single STRUCT and using other aggregate functions on that, but the other aggregate functions did not accept a STRUCT.

2

Contacts ARRAY< STRUCT< ContactId STRING, Name STRING, Phone STRING > >

It seems you define Contracts with three fields, ContradId, Name, and Phone but not with sort_col. So it seems clear why sort_col is not found.

3

TOPK defaults to STRUCT<sort_col, col0, col1, etc.> It doesn’t take the name provided and there is no way to override it from what I can tell.

4

Did you try to use contact->ContactId?

5

Yes, it gives the same error about not finding the field.

When using DESCRIBE I see the following part of the schema:

        "name": "CONTACTS",
        "schema": {
          "type": "ARRAY",
          "fields": null,
          "memberSchema": {
            "type": "STRUCT",
            "fields": [
              {
                "name": "sort_col",
                "schema": {
                  "type": "INTEGER",
                  "fields": null,
                  "memberSchema": null
                }
              },
              {
                "name": "col0",
                "schema": {
                  "type": "STRING",
                  "fields": null,
                  "memberSchema": null
                }
6

Hmmm… not sure from top of my head… maybe somebody else knows. Sorry.

7

This topic was automatically closed after 30 days. New replies are no longer allowed.