Happy Business Starts Here

Highlighted
Support SME

How to use Data Query to query records with null value or certain value?

Here is the document of the Data Query feature: Data Query

 

To find all records having a null value of a custom field and all records having a certain value of a custom field, we could use below 2 requests:

Submit data query

Get data query job

 

And here is an example test.

 

Submit the query:

POST https://rest.apisandbox.zuora.com/query/jobs

{
  "compression": "NONE",
  "output": {
    "target": "S3"
  },
  "outputFormat": "JSON",
  "query": "Select Id, CustomField1__c From CreditMemo where CustomField1__c is null or CustomField1__c='APPSPACE.US'"
}

{
  "data": {
    "id": "367897cd-7bbf-4b43-b889-93adad20baef",
    "query": "Select Id, CustomField1__c From CreditMemo where CustomField1__c is null or CustomField1__c='abcdefg'",
    "queryStatus": "accepted",
    "remainingRetries": 3,
    "retries": 3,
    "updatedOn": "2019-10-31T13:28:06.022Z"
  }
}

 

Get the query job:

GET https://rest.apisandbox.zuora.com/query/jobs/367897cd-7bbf-4b43-b889-93adad20baef

{
  "data": {
    "id": "367897cd-7bbf-4b43-b889-93adad20baef",
    "query": "Select Id, CustomField1__c From CreditMemo where CustomField1__c is null or CustomField1__c='abcdefg'",
    "queryStatus": "completed",
    "dataFile": "https://owl-auw2-sbx01-query-result.s3.us-west-2.amazonaws.com/367897cd-7bbf-4b43-b889-93adad20baef_7315050230754387.json?X-Amz-Security-Token=FQoGZXIvYXdzEN3%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FwEaDKAFIoLRBfTCo94MZSKVBBomUjfRM6wywG2hP3WmmBFNgV1ZN7PAKhAobIRQizjG7smIxgqvUghaYj9tZypUdAbNqhvMv2Zl8AlVF9ln5yneD%2FcIg%2BcTurco7L3wwVHCmZXJx%2FTF4ra92ES3yuA7sJ7uIlixqdOrvhDPmGsrogfF89xSoLNkY4DdVXDwxyc%2BkA5Am0xs9bFxP%2FLTE1clrajb5Q01aRLyaTx8Iu0Ot9hlPi9P9JnXUY%2FE219sBXI6XPU0iVy%2FGoVt%2BLqM%2FT9JvOK7lupsGJLULAVUkWvwF6XBD0jh6R0HDdht2HtftfabAYRWgDYhikiIwsBptTkYVVMXetej5fpSz7rHWbbCI5jBsioW6Id%2B79Ptnet5x5QRsXmdBxpRZHmvQ0%2BodCw262rL%2B6yJXEi%2FFK6ul%2FhMqSdYQZBGfIN95Fi2fV0TMjEJPzxeE7r88%2FBuVi9GF25zml9PtEcHjyFImPX8WZBHICekvLndfo8JAVtaDjVrJpQo2G11lEyQcVItfIjSN6dzJySCL7MqSXrFIDDs96uJCDR3nPoxg5VVeb94sWDhL9TY%2BKJminquDlAGWkvsRncVvIrXnSWTG3pqQEaDf8r6sRNrl5WEPEgEs5bmfXqunoInerGW68kRpe2uIhfjmvLn8FWNO9MdUxhPDoCJjD82%2BOJlKXSXvbfpHLVM0zuQmTEaX8oYjw%2Ft0EJUTlJODft58C3eCZa9KLaX6%2B0F&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Date=20191031T132807Z&X-Amz-SignedHeaders=host&X-Amz-Expires=604799&X-Amz-Credential=ASIAQXFLO6OTDH35BEOM%2F20191031%2Fus-west-2%2Fs3%2Faws4_request&X-Amz-Signature=e840fe0862737a24ed22c994ae61beabcb579d0b1f068c4fb5a6195e419d69f3",
    "outputRows": 12,
    "processingTime": 1201,
    "remainingRetries": 3,
    "retries": 3,
    "updatedOn": "2019-10-31T13:28:07.223Z"
  }
}

 

Fetch the dataFile with the URL in the response of query job and here is its content:

Screen Shot 2019-10-31 at 9.41.41 PM.png






If you found my answer helpful, please give me a kudo ↑
Help others find answers faster by accepting my post as a solution √