Query items from CosmosDB based on presense or absense of complex object property

In my previous article I showed how to query items from CosmosDB using conditions against nested properties: see Query nested data in CosmosDB. In this post I will show how to query items based on presence or absence of complex object property. Let's use the same example which was used in mentioned post:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

[{     "id": "1",     "title": "Foo",     "metadata": {         "metadataId": "123",         "fieldValues": [             {                 "fieldName": "field1",                 "values": [                     "val1"                 ]             },             {                 "fieldName": "field2",                 "values": [                     "val2"                 ]             },             {                 "fieldName": "field3",                 "values": [                     "val2"                 ]             }         ]     } }, {     "id": "2",     "title": "Bar", }, ... ]

So we have 2 objects Foo and Bar. Foo object has metadata property while Bar doesn't. How to fetch all objects which have metadata property? And vice versa: how to fetch those objects which don't have it?

The first attempt would be to compare it with null:

?

1	SELECT * FROM c WHERE c.metadata != null

And it works. However if we will try to use the same technique for fetching all items which don't have metadata the following query won't return any results:

?

1	SELECT * FROM c WHERE c.metadata = null

Better option which works in both cases will be to use helper function IS_DEFINED. With this function in order to fetch all items which have metadata property we may use the following query:

?

1	SELECT * FROM c WHERE IS_DEFINED(c.metadata)

And similarly we may fetch objects which don't have metadata:

?

1	SELECT * FROM c WHERE NOT IS_DEFINED(c.dynamicMetadata)

This technique is more universal since it works in both scenarios.

Query nested data in CosmosDB

Let's say we have objects in CosmosDB with complex nested structure:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

[{     "id": "1",     "title": "Foo",     "metadata": {         "metadataId": "123",         "fieldValues": [             {                 "fieldName": "field1",                 "values": [                     "val1"                 ]             },             {                 "fieldName": "field2",                 "values": [                     "val2"                 ]             },             {                 "fieldName": "field3",                 "values": [                     "val2"                 ]             }         ]     } }, ... ]

And we need to fetch items items by metadata values stored in field1, field2, etc. Using above example if we need to fetch objects which have metadata.metadataId = "123", field1.value = "val1" and field2.value = "val2" we should use the following query:

?

1 2 3 4 5 6 7

SELECT * FROM c WHERE c.metadata.metadataId = '123' AND EXISTS(SELECT VALUE fv.fieldName FROM fv IN c.dynamicMetadata.fieldValues WHERE fv.fieldName = 'field1' AND     EXISTS(SELECT VALUE v FROM v IN fv.values WHERE v = 'val1')) AND EXISTS(SELECT VALUE fv.fieldName FROM fv IN c.dynamicMetadata.fieldValues WHERE fv.fieldName = 'field2' AND         EXISTS(SELECT VALUE v FROM v IN fv.values WHERE v = 'val2'))

If values for some field contain multiple values we may construct even more complex conditions by combining conditions for single field values by AND or OR operators depending on requirements.

Export Sharepoint Online lists with their content to PnP template via PnP PowerShell

As you probably know it is possible to export Sharepoint Online sites to PnP template using Get-PnPSiteTemplate cmdlet. By default it will export only structure but it is also possible to export Sharepoint lists with content (list items) to PnP template. This is quite commonly needed request in many maintenance tasks. Of course you may save list as template with content from UI but if you need to automate it this option is not very convenient.

If you need to export Sharepoint list with its content to PnP template use the following commands:

?

1 2 3

Connect-PnPOnline -Url https://{mytenant}.sharepoint.com/sites/foo -Interactive Get-PnPSiteTemplate -Out template.pnp -ListsToExtract "Test" -Handlers Lists Add-PnPDataRowsToSiteTemplate -Path template.pnp -List "Test"

In this example we export list Test with list items to PnP template template.pnp. Hope that it will help someone.

Get functions invocations list for continuous Azure web job via REST API

This post is continuation of series of articles about continuous Azure web jobs. You may find previous articles about continuous web jobs here:

• How to run continuous Azure web job as singleton
• How to configure number of retries for processing failed Azure queue message before it will be moved to poison queue
• How to set limit on simultaneously processed queue messages with continuous Azure web job
  

In this post I will show how to get list of functions' invocations for continuous web job via REST API.

As you probably know (and as it was shown in one of above articles) when you work with continuous web job we need to implement function which will be triggered e.g. using Azure queue trigger. Log which is shown in Azure portal looks bit different for continuous web jobs comparing with timer triggered web jobs: every time when this handler function got triggered new invocation is added to the log. It contains details of this particular function's invocation:

In order to get this list of functions' invocations programmatically we may use WebJobs REST API and more specifically the following endpoint:

https://{app-service-name}.scm.azurewebsites.net/azurejobs/api/jobs/continuous/{webJob}/functions?limit={limit}

Response will look like that:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

{     "entries": [{             "executingJobRunId": null,             "id": "0399f7d5-a7aa-43c9-8853-70886174fda3",             "functionId": null,             "functionName": null,             "functionFullName": null,             "functionDisplayTitle": "ProcessQueueMessage (5540, )",             "status": "CompletedSuccess",             "whenUtc": "2022-05-03T12:12:05.3054119Z",             "duration": 101650.2167,             "exceptionMessage": null,             "exceptionType": null,             "hostInstanceId": "00000000-0000-0000-0000-000000000000",             "instanceQueueName": null         }, {             "executingJobRunId": null,             "id": "d581d6fa-8ba0-4c4d-ad39-c8505d21b851",             "functionId": null,             "functionName": null,             "functionFullName": null,             "functionDisplayTitle": "ProcessQueueMessage (5539, )",             "status": "CompletedSuccess",             "whenUtc": "2022-05-03T12:12:04.4842786Z",             "duration": 100893.4565,             "exceptionMessage": null,             "exceptionType": null,             "hostInstanceId": "00000000-0000-0000-0000-000000000000",             "instanceQueueName": null         }     ],     "continuationToken": null,     "isOldHost": false }

It contains array of objects which represent invocation details. Id property contains actual invocationId which may be used e.g. for getting invocation log by the following url:

https://{app-service-name}.scm.azurewebsites.net/azurejobs/#/functions/invocations/{invocationId}

Property functionDisplayTitle contains name of invocation together with passed parameter. It may hep if you need to map input parameters with invocation log. Also pay attention on continuationToken - if there are many invocations (more than passed in limit query string parameter) it will contain continuation token using which you may get whole list of invocations by pages.