Skip to content
AWS for Query.Farm

aws.customer_profiles.search_profiles

Example SQL Queries

SELECT * FROM
aws.customer_profiles.search_profiles
WHERE
"domain_name" = 'VALUE'
AND "key_name" = 'VALUE'
AND "values" = 'VALUE';

Description

Searches for profiles within a specific domain using one or more predefined search keys (e.g., _fullName, _phone, _email, _account, etc.) and/or custom-defined search keys. A search key is a data type pair that consists of a KeyName and Values list.

This operation supports searching for profiles with a minimum of 1 key-value(s) pair and up to 5 key-value(s) pairs using either AND or OR logic.

Table Definition

Column NameColumn Data Type
domain_name Required Input Column

The unique name of the domain.

VARCHAR
key_name Required Input Column

A searchable identifier of a customer profile. The predefined keys you can use to search include: _account, _profileId, _assetId, _caseId, _orderId, _fullName, _phone, _email, _ctrContactId, _marketoLeadId, _salesforceAccountId, _salesforceContactId, _salesforceAssetId, _zendeskUserId, _zendeskExternalId, _zendeskTicketId, _serviceNowSystemId, _serviceNowIncidentId, _segmentUserId, _shopifyCustomerId, _shopifyOrderId.

VARCHAR
values Required Input Column

A list of key values.

VARCHAR[]
Show child fields
values[]
additional_search_keys Input Column

A list of AdditionalSearchKey objects that are each searchable identifiers of a profile. Each AdditionalSearchKey object contains a KeyName and a list of Values associated with that specific key (i.e., a key-value(s) pair). These additional search keys will be used in conjunction with the LogicalOperator and the required KeyName and Values parameters to search for profiles that satisfy the search criteria. 

STRUCT(
    "key_name" VARCHAR,
    "values" VARCHAR[]
)[]
Show child fields
additional_search_keys[]
Show child fields
additional_search_keys[].key_name

A searchable identifier of a customer profile.

additional_search_keys[].values[]
logical_operator Input Column

Relationship between all specified search keys that will be used to search for profiles. This includes the required KeyName and Values parameters as well as any key-value(s) pairs specified in the AdditionalSearchKeys list.

This parameter influences which profiles will be returned in the response in the following manner:

  • AND - The response only includes profiles that match all of the search keys.

  • OR - The response includes profiles that match at least one of the search keys.

The OR relationship is the default behavior if this parameter is not included in the request.

VARCHAR
max_results Input Column

The maximum number of objects returned per page.

The default is 20 if this parameter is not included in the request.

BIGINT
next_token Input Column

The pagination token from the previous SearchProfiles API call.

VARCHAR
_aws_profile Input Column

The AWS profile defines the AWS identity used. It can be defined via credentials or by assuming a IAM role.

STRUCT(
    "type" VARCHAR,
    "name" VARCHAR,
    "account_id" VARCHAR,
    "via_profile_name" VARCHAR,
    "assumed_role_arn" VARCHAR,
    "organization" STRUCT(
        "account_name" VARCHAR,
        "id" VARCHAR,
        "tags" STRUCT(
            "key" VARCHAR,
            "value" VARCHAR
        )[],
        "master_account" STRUCT(
            "id" VARCHAR,
            "email" VARCHAR
        ),
        "parents" STRUCT(
            "type" VARCHAR,
            "id" VARCHAR,
            "name" VARCHAR,
            "tags" STRUCT(
                "key" VARCHAR,
                "value" VARCHAR
            )[]
        )[]
    )
)
Show child fields
_aws_profile.account_id

The AWS account id

_aws_profile.assumed_role_arn

The ARN of the assumed role

_aws_profile.name

The unique name of the profile.

_aws_profile.organization

Information about this profile's membership in the AWS organization.

Show child fields
_aws_profile.organization.account_name

The name of account speciifed by the organization

_aws_profile.organization.id

The organization id

_aws_profile.organization.master_account
Show child fields
_aws_profile.organization.master_account.email

The organization master account email address

_aws_profile.organization.master_account.id

The organization master account id

_aws_profile.organization.parents[]
Show child fields
_aws_profile.organization.parents[].id

The id of the parent

_aws_profile.organization.parents[].name

The name of the parent

_aws_profile.organization.parents[].tags[]
Show child fields
_aws_profile.organization.parents[].tags[].key
_aws_profile.organization.parents[].tags[].value
_aws_profile.organization.parents[].type

The type of parent can be an organization unit or a root

_aws_profile.organization.tags[]
Show child fields
_aws_profile.organization.tags[].key
_aws_profile.organization.tags[].value
_aws_profile.type

The type of profile, either 'credentials' or 'assumed_role'

_aws_profile.via_profile_name

This IAM role for this profile is assumed by first utilizing another profile with this name to obtain credentials.

items

The list of Profiles matching the search criteria.

STRUCT(
    "profile_id" VARCHAR,
    "account_number" VARCHAR,
    "additional_information" VARCHAR,
    "party_type" VARCHAR,
    "business_name" VARCHAR,
    "first_name" VARCHAR,
    "middle_name" VARCHAR,
    "last_name" VARCHAR,
    "birth_date" VARCHAR,
    "gender" VARCHAR,
    "phone_number" VARCHAR,
    "mobile_phone_number" VARCHAR,
    "home_phone_number" VARCHAR,
    "business_phone_number" VARCHAR,
    "email_address" VARCHAR,
    "personal_email_address" VARCHAR,
    "business_email_address" VARCHAR,
    "address" STRUCT(
        "address1" VARCHAR,
        "address2" VARCHAR,
        "address3" VARCHAR,
        "address4" VARCHAR,
        "city" VARCHAR,
        "county" VARCHAR,
        "state" VARCHAR,
        "province" VARCHAR,
        "country" VARCHAR,
        "postal_code" VARCHAR
    ),
    "shipping_address" STRUCT(
        "address1" VARCHAR,
        "address2" VARCHAR,
        "address3" VARCHAR,
        "address4" VARCHAR,
        "city" VARCHAR,
        "county" VARCHAR,
        "state" VARCHAR,
        "province" VARCHAR,
        "country" VARCHAR,
        "postal_code" VARCHAR
    ),
    "mailing_address" STRUCT(
        "address1" VARCHAR,
        "address2" VARCHAR,
        "address3" VARCHAR,
        "address4" VARCHAR,
        "city" VARCHAR,
        "county" VARCHAR,
        "state" VARCHAR,
        "province" VARCHAR,
        "country" VARCHAR,
        "postal_code" VARCHAR
    ),
    "billing_address" STRUCT(
        "address1" VARCHAR,
        "address2" VARCHAR,
        "address3" VARCHAR,
        "address4" VARCHAR,
        "city" VARCHAR,
        "county" VARCHAR,
        "state" VARCHAR,
        "province" VARCHAR,
        "country" VARCHAR,
        "postal_code" VARCHAR
    ),
    "attributes" MAP(VARCHAR, VARCHAR),
    "found_by_items" STRUCT(
        "key_name" VARCHAR,
        "values" VARCHAR[]
    )[],
    "party_type_string" VARCHAR,
    "gender_string" VARCHAR
)[]
Show child fields
items[]
Show child fields
items[].account_number

An account number that you have given to the customer.

items[].additional_information

Any additional information relevant to the customer’s profile.

items[].address

A generic address associated with the customer that is not mailing, shipping, or billing.

Show child fields
items[].address.address1

The first line of a customer address.

items[].address.address2

The second line of a customer address.

items[].address.address3

The third line of a customer address.

items[].address.address4

The fourth line of a customer address.

items[].address.city

The city in which a customer lives.

items[].address.country

The country in which a customer lives.

items[].address.county

The county in which a customer lives.

items[].address.postal_code

The postal code of a customer address.

items[].address.province

The province in which a customer lives.

items[].address.state

The state in which a customer lives.

items[].attributes

A key value pair of attributes of a customer profile.

items[].billing_address

The customer’s billing address.

Show child fields
items[].billing_address.address1

The first line of a customer address.

items[].billing_address.address2

The second line of a customer address.

items[].billing_address.address3

The third line of a customer address.

items[].billing_address.address4

The fourth line of a customer address.

items[].billing_address.city

The city in which a customer lives.

items[].billing_address.country

The country in which a customer lives.

items[].billing_address.county

The county in which a customer lives.

items[].billing_address.postal_code

The postal code of a customer address.

items[].billing_address.province

The province in which a customer lives.

items[].billing_address.state

The state in which a customer lives.

items[].birth_date

The customer’s birth date.

items[].business_email_address

The customer’s business email address.

items[].business_name

The name of the customer’s business.

items[].business_phone_number

The customer’s home phone number.

items[].email_address

The customer’s email address, which has not been specified as a personal or business address.

items[].first_name

The customer’s first name.

items[].found_by_items[]
Show child fields
items[].found_by_items[].key_name

A searchable identifier of a customer profile.

items[].found_by_items[].values[]
items[].gender

The gender with which the customer identifies.

items[].gender_string

An alternative to Gender which accepts any string as input.

items[].home_phone_number

The customer’s home phone number.

items[].last_name

The customer’s last name.

items[].mailing_address

The customer’s mailing address.

Show child fields
items[].mailing_address.address1

The first line of a customer address.

items[].mailing_address.address2

The second line of a customer address.

items[].mailing_address.address3

The third line of a customer address.

items[].mailing_address.address4

The fourth line of a customer address.

items[].mailing_address.city

The city in which a customer lives.

items[].mailing_address.country

The country in which a customer lives.

items[].mailing_address.county

The county in which a customer lives.

items[].mailing_address.postal_code

The postal code of a customer address.

items[].mailing_address.province

The province in which a customer lives.

items[].mailing_address.state

The state in which a customer lives.

items[].middle_name

The customer’s middle name.

items[].mobile_phone_number

The customer’s mobile phone number.

items[].party_type

The type of profile used to describe the customer.

items[].party_type_string

An alternative to PartyType which accepts any string as input.

items[].personal_email_address

The customer’s personal email address.

items[].phone_number

The customer's phone number, which has not been specified as a mobile, home, or business number.

items[].profile_id

The unique identifier of a customer profile.

items[].shipping_address

The customer’s shipping address.

Show child fields
items[].shipping_address.address1

The first line of a customer address.

items[].shipping_address.address2

The second line of a customer address.

items[].shipping_address.address3

The third line of a customer address.

items[].shipping_address.address4

The fourth line of a customer address.

items[].shipping_address.city

The city in which a customer lives.

items[].shipping_address.country

The country in which a customer lives.

items[].shipping_address.county

The county in which a customer lives.

items[].shipping_address.postal_code

The postal code of a customer address.

items[].shipping_address.province

The province in which a customer lives.

items[].shipping_address.state

The state in which a customer lives.