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:

[{
    "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:

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:

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:

SELECT * FROM c WHERE IS_DEFINED(c.metadata)

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

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:

[{
	"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:

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.

T-SQL script for deleting big amount of records from table in SQL server database

Sometimes we need to delete big amount of records from the table in SQL Server database. If number of records is quite big (several millions) the simple delete command will hang:

   1: delete from Foo

In this case we can use the following approach: delete records by chunks (e.g. 100000 items per operation, but it depends on how many columns your table has. It if has a lot of columns and data, you may adjust this number for your scenario) until all records will be deleted. For example imagine that we have table [Log] used for logging some operations:

   1: create table [Log](

   2:     [Id] [int] identity(1,1) not null,

   3:     [ThreadId] [int] not null,

   4:     [EventType] [nvarchar](32) null,

   5:     [Message] [nvarchar](max) null,

   6:     [Created] [datetime] not null

   7: )

And suppose that this table grows quite fast and we need periodically delete old log records from it (e.g. older than 2 weeks). It can be done by creating job in SQL Server agent. For this job create the step which will run the following code:

   1: declare @s datetime

   2: set @s = GETDATE() - 14

   3: declare @cnt int

   4:  

   5: set @cnt = (select count(*) from [Log] where Created < @s)

   6: while @cnt > 0

   7: begin

   8:     delete top (100000)

   9:     from [Log]

  10:     where Created < @s

  11:     

  12:     set @cnt = (select count(*) from [Log] where Created < @s)

  13: end

This script will delete records from the Log table by chunks in 100000 rows until all records which are older than 2 weeks won’t be deleted and won’t hang because of big amount of data.