Where Clause in Rest API Query

carlcimino — Thu, 08 Dec 2022 01:56:39 GMT

I have a working rest connection against a Azure Cosmos DB API. What I am trying to do is include a where clause on the "_ts" field (time stamp in a unix format). The reason for doing this is so I don't reload the entire API contents every time and set up weekly incremental loads. Later on in the UI dashboard I convert this to a normal date format using date(floor([_ts]/86400)+25569,'YYYY-MM-DD') as Date. My issue is I can not seem to figure out where to put it and with what syntax. Any help would be appreciated.

Week_Period_List:
load
date(weekstart(Today()),'YYYY-MM-DD') as Week_Period
AutoGenerate(1)
;

concatenate(Week_Period_List)

load
date(weekstart((Today()-6)),'YYYY-MM-DD') as Week_Period
AutoGenerate(1)
;

FOR v_RowCounter = 0 TO NoOfRows('Week_Period_List')-1;

LET v_WeekPeriod = peek('Week_Period',$(v_RowCounter),'Week_Period_List');

LIB CONNECT TO 'REST_CosmosDB_Data_Retrieve';

RestConnectorMasterTable:
SQL SELECT
"_rid" AS "_rid_u0",
"__KEY_root",
(SELECT
"id",
"RequestPath",
"GatewayType",
"SourceTransactionId",
"ChannelId",
"AppId",
"Status",
"IsSuccess",
"TransactionAmount",
"ResponseCode",
"Duration",
"_rid",
"_self",
"_etag",
"_attachments",
"_ts", <-- Unix time stamp formatted like this 1670112040 is really 2022-12-04. I want to pass the v_WeekPeriod variable in a where clause
"PolicyNumber",
"BillingAccountNumber",
"ErrorMessages",
"Exception",
"__KEY_Documents",
"__FK_Documents",
(SELECT
"ProcessorResponseCode",
"ProcessorResponseText",
"TransactionId",
"__FK_TransactionLog"
FROM "TransactionLog" FK "__FK_TransactionLog")
FROM "Documents" PK "__KEY_Documents" FK "__FK_Documents")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(url "$(v_EnvironmentURL)",
HTTPHEADER "Authorization" "$(vSessionId)",
HTTPHEADER "x-ms-date" "$(vMS_Date)",
HTTPHEADER "x-ms-version" "$(vMS_Version)",
HTTPHEADER "x-ms-max-item-count" "10" )
;

next v_RowCounter

topic Where Clause in Rest API Query in Connectivity & Data Prep

Where Clause in Rest API Query