filter data from json

Abedin, Masood 20

I am new to the Azure Data Factory. I have an UpdatedDate column in this format,

[
  638137843666973190,
  60
]

In the copy data, filter property, I have this code

{
UpdatedDate: {
$gte: ISODate('@{formatDateTime(pipeline().parameters.windowStart)}'),
$lt: ISODate('@{formatDateTime(pipeline().parameters.windowEnd)}')}
}

what formula should I assign as a Default value for windowStart and End parameters.

Thank you.

GeethaThatipatri-MSFT 27,012 Reputation points Microsoft Employee

2023-03-15T21:25:20.5033333+00:00

Hi, @Abedin, Masood Welcome to the Microsoft Q&A platform, thanks for posting the question

Can you try using utcNow function to get the current timestamp and then use the addToTime function to add or subtract time units from the current timestamp

Something like this

formatDateTime(addToTime(utcNow(), -7, 'Day')) change as per your requirement.

Regards

Geetha

Abedin, Masood 20

I am getting the error message

In function 'formatDateTime', the value provided for date time string 'formatDateTime(addToTime(utcNow(), -7, 'Day')) ' was not valid. The datetime string must match ISO 8601 format

Screenshot 2023-03-15 at 9.25.07 PM

This is what I have in the source filter

Screenshot 2023-03-15 at 11.14.07 AM

Here is the code for copy data:

{
    "name": "Copy_343",
    "type": "Copy",
    "dependsOn": [],
    "policy": {
        "timeout": "0.12:00:00",
        "retry": 0,
        "retryIntervalInSeconds": 30,
        "secureOutput": false,
        "secureInput": false
    },
    "userProperties": [
        {
            "name": "Source",
            "value": "workorder.notes"
        },
        {
            "name": "Destination",
            "value": "cosmos-workorder-notes//workorder-notes.json"
        }
    ],
    "typeProperties": {
        "source": {
            "type": "CosmosDbMongoDbApiSource",
            "batchSize": 100,
            "filter": {
                "value": "{\nUpdatedDate: {\n$gte: ISODate('@{formatDateTime(pipeline().parameters.windowStart)}'),\n$lt: ISODate('@{formatDateTime(pipeline().parameters.windowEnd)}')}\n}",
                "type": "Expression"
            }
        },
        "sink": {
            "type": "JsonSink",
            "storeSettings": {
                "type": "AzureBlobStorageWriteSettings"
            },
            "formatSettings": {
                "type": "JsonWriteSettings",
                "filePattern": "setOfObjects"
            }
        },
        "enableStaging": false,
        "validateDataConsistency": false
    },
    "inputs": [
        {
            "referenceName": "CosmosDbMongoDbCollectionKuldeep",
            "type": "DatasetReference"
        }
    ],
    "outputs": [
        {
            "referenceName": "mabedin_dataset",
            "type": "DatasetReference"
        }
    ]
}

Accepted answer

GeethaThatipatri-MSFT 27,012 Reputation points Microsoft Employee

2023-03-16T20:27:39.13+00:00

@Abedin, Masood Thanks for providing additional information

Are you using a tumbling window? if yes you can follow the steps from this link

from the pipeline go to Edit trigger and from there go to trigger run parameters as shown below

This splits a trigger and assigns values to the pipeline parameter corresponding to the triggers window. Now you do not need to assign a default value to the pipeline parameter.

The format looks like "2023-03-16T19:46:00Z"

If you are not using a tumbling window do not try to evaluate expression in the pipeline parameter. insted either use a veriable or evaluate directly filter like shown below.

use trigger time insted of utcnow for greater consistency, refer to this link for more information.

Regards

Geetha
Please sign in to rate this answer.

1 person found this answer helpful.
Abedin, Masood 20 Reputation points

2023-03-17T06:00:38.5666667+00:00

Thanks for your assistance I was able to filter rows but In order to get the rows I had modify the expression as below:

{ UpdatedDate: { $gte: @{ticks(formatDateTime(addToTime(pipeline().TriggerTime, -4, 'Day')))}, $lt: @{ticks(formatDateTime(addToTime(pipeline().TriggerTime, -2, 'Day')))}} }

In the above, is that a boolean condition, I am trying to write a filter that will behave like WHERE clause

GeethaThatipatri-MSFT 27,012 Reputation points Microsoft Employee

2023-03-21T15:21:33.3133333+00:00

Abedin, Masood Goot to know that you were able to filter rows now.

The filter you mentioned specifies that it should return all rows where the Update Date field is greater than or equal to 4 days ago and less than 2 days ago. The $gte operator specifies that the value must be greater than or equal to the specified value, and the $lt operator specifies that the value must be less than the specified value

Where Updated Date is < 2 days ago and where Updated Date >= 4 days ago

If your question has been answered, please click Accept answer and Yes to help others looking for answers to similar questions.

Regards

Geetha
Sign in to comment

filter data from json

0 additional answers