Retrieve a Query Activity verified

Learn how to retrieve a Query Activity in Salesforce Marketing Cloud (SFMC) with SSJS (server-side JavaScript). Code snippets include Core, WSProxy and REST API methods.

Core

var customerKey = "S0M3-GU1D-K3Y-G03SR1G4T-H3R3";

var filter = {
    Property: "CustomerKey",
    SimpleOperator: "equals",
    Value: customerKey
}

var result = QueryDefinition.Retrieve(filter);

<script runat="server">

    Platform.Load("core", "1");

	try {

        var customerKey = "S0M3-GU1D-K3Y-G03SR1G4T-H3R3";

        var filter = {
            Property: "CustomerKey",
            SimpleOperator: "equals",
            Value: customerKey
        }

        var result = QueryDefinition.Retrieve(filter);

        Write(Stringify(result));
		
	} catch(error) {

        Write(Stringify(error));
        
    }	

</script>

[
    {
        "Name": "MyNewQuery",
        "DataExtensionTarget": {
            "Name": "MyDataExtension",
            "Description": "",
            "Keyword": null,
            "Client": null,
            "PartnerKey": null,
            "PartnerProperties": null,
            "CreatedDate": "0001-01-01T00:00:00.000",
            "CreatedDateSpecified": false,
            "ModifiedDate": null,
            "ModifiedDateSpecified": false,
            "ID": 0,
            "IDSpecified": false,
            "ObjectID": null,
            "CustomerKey": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
            "Owner": null,
            "CorrelationID": null,
            "ObjectState": null,
            "IsPlatformObject": false,
            "IsPlatformObjectSpecified": false
        },
        "ObjectID": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
        "QueryText": "SELECT TOP 50 SubscriberKey, EmailAddress FROM _Subscribers",
        "TargetType": "DE",
        "TargetUpdateType": "Overwrite",
        "FileSpec": "",
        "FileType": "",
        "Status": "Active",
        "CategoryID": 0,
        "CategoryIDSpecified": false,
        "InteractionObjectID": null,
        "Description": "My updated query",
        "Keyword": null,
        "Client": null,
        "PartnerKey": null,
        "PartnerProperties": null,
        "CreatedDate": "2022-12-07T13:05:21.770",
        "CreatedDateSpecified": true,
        "ModifiedDate": "2022-12-08T10:04:58.837",
        "ModifiedDateSpecified": true,
        "ID": 0,
        "IDSpecified": false,
        "CustomerKey": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
        "Owner": null,
        "CorrelationID": null,
        "ObjectState": null,
        "IsPlatformObject": false,
        "IsPlatformObjectSpecified": false
    }
]

WSProxy

var api = new Script.Util.WSProxy();

var name = "MyNewQuery";

var cols = [
    "CategoryID",
    "CreatedDate",
    "CustomerKey",
    "Description",
    "DataExtensionTarget.Name",
    "DataExtensionTarget.CustomerKey",
    "FileSpec",
    "FileType",
    "ModifiedDate",
    "Name",
    "ObjectID",
    "QueryText",
    "Status",
    "TargetType",
    "TargetUpdateType"
];

var filter = {
    Property: "Name", 
    SimpleOperator: "equals",
    Value: name 
};

var result = api.retrieve("QueryDefinition", cols, filter);

<script runat="server">

    Platform.Load("core", "1");

    var api = new Script.Util.WSProxy();

    try {

        var name = "MyNewQuery";

        var cols = [
            "CategoryID",
            "CreatedDate",
            "CustomerKey",
            "Description",
            "DataExtensionTarget.Name",
            "DataExtensionTarget.CustomerKey",
            "FileSpec",
            "FileType",
            "ModifiedDate",
            "Name",
            "ObjectID",
            "QueryText",
            "Status",
            "TargetType",
            "TargetUpdateType"
        ];

        var filter = {
            Property: "Name", 
            SimpleOperator: "equals",
            Value: name 
        };

        var result = api.retrieve("QueryDefinition", cols, filter);

        Write(Stringify(result));

    } catch (error) {

        Write(Stringify(error));

    }

</script>

{
    "Status": "OK",
    "RequestID": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "Results": [
        {
            "Name": "MyNewQuery",
            "ObjectID": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
            "QueryText": "SELECT TOP 5 SubscriberKey, EmailAddress FROM _Subscribers",
            "TargetType": "DE",
            "DataExtensionTarget": {
                "Name": "MyDataExtension",
                "Description": null,
                "Keyword": null,
                "Client": null,
                "PartnerKey": null,
                "PartnerProperties": null,
                "CreatedDate": "0001-01-01T00:00:00.000",
                "ModifiedDate": null,
                "ID": 0,
                "ObjectID": null,
                "CustomerKey": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
                "Owner": null,
                "CorrelationID": null,
                "ObjectState": null,
                "IsPlatformObject": false
            },
            "TargetUpdateType": "Overwrite",
            "FileSpec": "",
            "FileType": "",
            "Status": "Active",
            "CategoryID": 12345,
            "InteractionObjectID": null,
            "Description": "My updated queery",
            "Keyword": null,
            "Client": null,
            "PartnerKey": null,
            "PartnerProperties": null,
            "CreatedDate": "2022-12-07T13:05:21.769",
            "ModifiedDate": "2022-12-07T13:51:13.500",
            "ID": 0,
            "CustomerKey": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
            "Owner": null,
            "CorrelationID": null,
            "ObjectState": null,
            "IsPlatformObject": false
        }
    ],
    "HasMoreRows": false
}

REST API

var queryDefinitionId =  "S0M3-GU1D-K3Y-G03SR1G4T-H3R3";

var endpoint = restInstanceUrl + "automation/v1/queries/" + queryDefinitionId;

var request = new Script.Util.HttpRequest(endpoint);
    request.emptyContentHandling = 0;
    request.retries = 2;
    request.continueOnError = true;
    request.setHeader("Authorization", "Bearer " + accessToken);
    request.method = "GET";
    request.contentType = "application/json";
    request.encoding = "UTF-8";

var results = request.send();

var result = Platform.Function.ParseJSON(String(results.content));

<script runat="server">

    Platform.Load("core", "1");

    var api = new Script.Util.WSProxy();

    var restInstanceUrl = "https://YOUR_SUBDOMAIN.rest.marketingcloudapis.com/",
        accessToken     = "YOUR_REST_API_TOKEN";
	
	try {

        var queryDefinitionId =  "S0M3-GU1D-K3Y-G03SR1G4T-H3R3";

        var endpoint = restInstanceUrl + "automation/v1/queries/" + queryDefinitionId;

        var request = new Script.Util.HttpRequest(endpoint);
            request.emptyContentHandling = 0;
            request.retries = 2;
            request.continueOnError = true;
            request.setHeader("Authorization", "Bearer " + accessToken);
            request.method = "GET";
            request.contentType = "application/json";
            request.encoding = "UTF-8";

        var results = request.send();

        var result = Platform.Function.ParseJSON(String(results.content));

        Write(Stringify(result));
		
	} catch(error) {
        Write(Stringify(error));
    }	

</script>

{
    "queryDefinitionId": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "name": "MyNewQuery",
    "key": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "description": "My updated queery",
    "queryText": "SELECT TOP 5 SubscriberKey, EmailAddress FROM _Subscribers",
    "targetName": "MyDataExtension",
    "targetKey": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "targetId": "S0M3-GU1D-K3Y-G03SR1G4T-H3R3",
    "targetDescription": "",
    "createdDate": "2022-12-07T13:05:21.77",
    "modifiedDate": "2022-12-07T13:51:13.5",
    "targetUpdateTypeId": 0,
    "targetUpdateTypeName": "Overwrite",
    "validatedQueryText": "SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;\r\n\r\nINSERT INTO C100000000.[MyDataExtension] ([SubscriberKey], [EmailAddress])\r\nSELECT querydef.[SubscriberKey], querydef.[EmailAddress]\r\nFROM (SELECT TOP 5 SubscriberKey, EmailAddress FROM C100000000._Subscribers) AS querydef \r\nSELECT @rcInsert = @@ROWCOUNT;;\r\n",
    "categoryId": 12345,
    "isFrozen": false
}

TIP

queryDefinitionId can be found in the URL when accessing the Query Activity.

Reference

Ressources and references related to the current methods.

SOAP object