wixData.aggregate not working with external database (Google Cloud )

Ask the community
,
1

After succesful connection by Wix adapter to external database (Google Cloud / postgres) found that following code, using .aggregate resulting error “Error: WDE0116: fields don’t exist”.
Same time - .find() working.
All permissions on Cloud side given.
Several attempts (later with Wix Chat Bot) to change external collections / names / field types etc - same error (if collection / fields name correct)

export function aggregate() {
console.log(“Running aggregate()”);

wixData.aggregate("Accounting/ssd_ready_production")
    .count()
    .run()
    .then((results) => {
        console.log("Aggregation results: ", results);
        if (results.items.length > 0) {
            console.log("Count: " + results.items[0]._count);
        } else {
            console.log("No items found.");
        }
    })
    .catch((err) => {
        console.log("Error: " + err.message);
        console.log("Code: " + err.code);
    });

}

External dataset:
“id”:“ssd_ready_production”,
“displayName”:“ssd_ready_production”,
“allowedOperations”:[
“get”,
“find”,
“count”,
“update”,
“insert”,
“remove”,
“bulkRemove”,
“bulkInsert”,
“bulkUpdate”
],
“allowedSchemaOperations”:[
“list”,
“listHeaders”,
“createCollection”,
“dropCollection”,
“addColumn”,
“removeColumn”,
“describeCollection”,
“findWithSort”,
“aggregate”,
“bulkDelete”,
“truncate”,
“updateImmediately”,
“deleteImmediately”,
“startWithCaseSensitive”,
“startWithCaseInsensitive”,
“projection”,
“findObject”,
“matches”,
“not”,
“include”,
“filterByEveryField”
],

fields

2

The issue likely arises from how Wix interprets the schema of your external database for the .aggregate() method. Start by ensuring that your database field names match exactly what Wix expects, as they are case-sensitive and should avoid special characters. Verify the schema in Wix by checking the external collection fields, and refresh it if needed. Test a basic .aggregate() query, such as grouping by a simple field like id, to confirm functionality before adding complexity. Use .find() to log a sample of the data structure to validate field names. Additionally, confirm that your database adapter fully supports .aggregate() and is not just granting permissions without implementation. If the issue persists, try removing and re-adding the external collection in Wix to refresh the schema and eliminate sync issues. If none of these steps resolve the problem, don’t hesitate to reach out for further guidance.

3

.find() showing structure:

vessel AH
_createddate null
number_of_boxes 85
_id 04b4e678-e8e7-45f3-aa96-65039b97f4e9
_owner b5c80855-4a9f-4fef-b935-0ba6d3674ffa
fish_group PBC
total_weight 2295
fish_type Mullet
box_unit_weight 27
_updateddate null
date_of_reporting Wed Nov 27 2024 23:00:00 GMT+0300 (Москва, стандартное время)
fish_size N/A
4

simple .aggregate() (belowdown) resulting same “Error: WDE0116: fields don’t exist”

export function aggregate6() {
console.log(“Running basic aggregate() with full collection name”);

wixData.aggregate("Accounting/ssd_ready_production")
    .count()
    .run()
    .then((results) => {
        console.log("Aggregation results: ", results);
        if (results.items.length > 0) {
            console.log("Count: " + results.items[0]._count);
        } else {
            console.log("No items found.");
        }
    })
    .catch((err) => {
        console.log("Error: " + err.message);
        console.log("Code: " + err.code);
        console.log("Error Details: ", err);
    });

}

5

Removing and re-adding the external collection in Wix (disconnect / re-connect) does not help.
https://kostimpasv.wixsite.com/website-1-1/aggregate
Different variants of aggregetion (as suggested by Wix Chat Boat) - same error, .find() working. Same story for other databases in same Cloud^ .find() working, .aggregate - not working.

6

I see your problem seem to be a little complex can we schedule a meeting to discuss everything is a better detailed form so that we can further help you with that

7

With my pleasure)) best time - working days 08:00 - 15:00 UTC
Thanks a lot)

8

@asuskiper
in telegramm

9

sure i will connect with you there .