Happy Business Starts Here

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

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 √