Skip to content
This page View Markdown Open in ChatGPT Open in Claude

Distinct with query

g.sys.db.distinctQuery calls the distinct with query schema API of any table from your code, with the same headers and query parameters as over HTTP. It returns the data, or the whole envelope when true is passed as the second argument.

Simple get distinctWithQuery

Simple distinctWithQuery
1
2
3
4
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name"
});

distinctWithQuery with multiple distinctField

distinctWithQuery with multiple distinctField
1
2
3
4
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name,last_name"
});

distinctWithQuery with headers

distinctWithQuery with headers
1
2
3
4
5
6
7
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name",
    headers: {
        "x-am-response-case": "capitalCase", // noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
    },
});

distinctWithQuery with order

ascending

distinctWithQuery with ascending order
1
2
3
4
5
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name",
    order: "asc"
});

descending

distinctWithQuery with descending order
1
2
3
4
5
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name",
    order: "desc"
});

distinctWithQuery with find

distinctWithQuery with find
1
2
3
4
5
6
7
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name",
    find: {
        first_name: "Bob"
    }
});

$lt distinctWithQuery

distinctWithQuery with $lt
1
2
3
4
5
6
7
8
9
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name",
    find: {
        customer_id: {
            "$lt": 2
        }
    }
});

$lte distinctWithQuery

distinctWithQuery with $lte
1
2
3
4
5
6
7
8
9
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name",
    find: {
        customer_id: {
            "$lte": 2
        }
    }
});

$gt distinctWithQuery

distinctWithQuery with $gt
1
2
3
4
5
6
7
8
9
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name",
    find: {
        customer_id: {
            "$gt": 2
        }
    }
});

$gte distinctWithQuery

distinctWithQuery with $gte
1
2
3
4
5
6
7
8
9
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name",
    find: {
        customer_id: {
            "$gte": 2
        }
    }
});

$eq distinctWithQuery

distinctWithQuery with $eq
1
2
3
4
5
6
7
8
9
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name",
    find: {
        customer_id: {
            "$eq": 2
        }
    }
});

$ne distinctWithQuery

distinctWithQuery with $ne
1
2
3
4
5
6
7
8
9
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name",
    find: {
        customer_id: {
            "$ne": 2
        }
    }
});

$not distinctWithQuery

distinctWithQuery with $not
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name",
    find: {
        customer_id: {
            "$not": {
                "$in": [1,2,3]
            }
        }
    }
});

$and distinctWithQuery

distinctWithQuery with $and
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name",
    find: {
        "$and": [
            {
                "customer_id": 1
            },
            {
                "zipcode": 382345
            }
        ]
    }
});

$or distinctWithQuery

distinctWithQuery with $or
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name",
    find: {
        "$or": [
            {
                "customer_id": 1
            },
            {
                "zipcode": 382345
            }
        ]
    }
});

$in distinctWithQuery

distinctWithQuery with $in
1
2
3
4
5
6
7
8
9
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name",
    find: {
        customer_id: {
            "$in": [1, 2,3]
        }
    }
});

$nin distinctWithQuery

distinctWithQuery with $nin
1
2
3
4
5
6
7
8
9
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name",
    find: {
        customer_id: {
            "$nin": [1, 2,3]
        }
    }
});

$like distinctWithQuery

distinctWithQuery with $like
1
2
3
4
5
6
7
8
9
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "mysql_8", database: "inventory", collection: "customers",
    distinctField: "first_name",
    find: {
        first_name: {
            "$like": "Bob%"
        }
    }
});

Structure

Distinct Query
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "INSTANCE_NAME", database: 'DB_NAME', collection: "COLLECTION_NAME",
    distinctField: "COLUMN_NAME",
    order: "asc", // asc | desc
    find: {
        "COLUMN_NAME": "VALUE",
    },
    headers: {
        "x-am-response-case": "capitalCase", // noChange | camelCase | capitalCase | constantCase | dotCase | headerCase | noCase | paramCase | pascalCase | pathCase | sentenceCase | snakeCase
        "x-am-response-object-type": "make_flat", // no_action | make_flat
        "x-am-meta": "true", // true, false
        "x-am-internationalization": "USER_I18N_ID",
        "x-am-run-in-sandbox": "2",
        "x-am-content-type-response": "text/xml", // application/json | text/xml | text/yaml | text/plain | text/html | application/octet-stream
        "x-am-cache-control": "reset_cache", // no_action | reset_cache
        "x-am-get-encrypted-data": "get_only_encryption", // no_encryption | get_only_encryption | get_data_and_encryption
        "x-am-sandbox-timeout": "13000",
        "x-no-compression": "true", // true | false
        "x-am-encrypted-payload": "true", // user will send 'true', when payload is encrypted for transfer
        // Multi-tenant : the tenant of the request, each instance finds it in its own tenants table
        "x-am-tenant-username": "TENANT_USERNAME", // ex: acme
        // Authorization headers
        "x-am-authorization": "AUTHORIZATION_TOKEN",
        "x-am-user-authorization": "API_USER_TOKEN",
        "x-aws-authorization": "AWS_TOKEN",
        "x-google-authorization": "GOOGLE_TOKEN",
        "x-azure-authorization": "AZURE_TOKEN",
    },
});

Multi-tenant

  • instance: "crm::acme" works on the database of the tenant acme of the multi-tenant instance crm.
  • With instance: "crm", the call works on the database of the tenant of the request: a custom API called with the x-am-tenant-username header passes it on to the APIs it calls. A request without a tenant works on the structure database.
Distinct first names of some customers of the tenant acme
1
2
3
4
5
6
7
let distinctQuery = await g.sys.db.distinctQuery({
    instance: "crm::acme", database: "crm", collection: "customers",
    distinctField: "first_name",
    find: {
        "last_name": "Lin"
    }
});