How to write SQL Query in Azure cosmos DB to find search pattern of any user

Question

Below is a sample item from azure cosmos DB container, I'm trying to write sql to get below expected output but unable to get it, can you help to write correct sql please. your help is greatly appreciated.

SELECT c.type, c.saveToUserEmail, ARRAY_CONCAT(s.fieldName, ',') AS fieldNameCombination, COUNT(1) AS count FROM c JOIN s IN c.searchCriteria.search WHERE c.type = 'CHANGEREPORT' GROUP BY c.type, c.saveToUserEmail, ARRAY_CONCAT(s.fieldName, ',')

Output I'm getting:

[
    {
        "type": "CHANGEREPORT",
        "saveToUserEmail": "[email protected]",
        "count": 1
    }
]

Expected output I want: (Note: In output unique fieldNameCombination value is preferable)

[
    {
        "type": "CHANGEREPORT",
        "saveToUserEmail": "[email protected]",
        "fieldNameCombination" : "vendorCode","changeDate",
        "count": 1
    }
]

Sample container item below:

    "name": "Change Report",
    "type": "CHANGEREPORT",
    "saveToUserEmail": "[email protected]",
    "isFavourite": "Y",
    "searchCriteria": {
        "fields": [
            "vendorCode",
            "poNumber",
            "itemNumber",
            "itemText"
        ],
        "search": [
            {
                "fieldName": "vendorCode",
                "operator": "=",
                "fieldValue": [
                    "IR"
                ]
            },
            {
                "fieldName": "changeDate",
                "operator": ">=",
                "fieldValue": "2021-01-01"
            },
            {
                "fieldName": "changeDate",
                "operator": "<=",
                "fieldValue": "2021-11-02"
            }
        ],
        "filter": [],
        "count": 1000,
        "offset": "0"
    },
    "id": "f00001f8f-2dec-41e0-b6b9-728eb6d8e123",
    "saveCriteriaID": "f8577f8f-2dec-41e0-b6b9-728eb6d8e847",
    "_rid": "F15IANpVIe0BAAAAAAAAAA==",
    "_self": "dbs/F15IAA==/colls/F15IANpVIe0=/docs/F15IANpVIe0BAAAAAAAAAA==/",
    "_etag": "\"44007a88-0000-1800-0000-6180c01a0000\"",
    "_attachments": "attachments/",
    "_ts": 1635827738
}```

Answer 1

In order to get the distinct fieldName values in fieldNameCombination key, you can use the below code.

Code:

SELECT c.type, c.saveToUserEmail,
ARRAY(select  distinct  value s.fieldName from c JOIN s IN c.searchCriteria.search) AS fieldNameCombination,
ARRAY_LENGTH(ARRAY(select  distinct  value s.fieldName from c JOIN s IN c.searchCriteria.search)) AS  count
FROM c where c.type='CHANGEREPORT'

This query creates an array of distinct fieldName values from the searchCriteria.search array for each document, and calculates the length of that array. The ARRAY function is used to create an array of distinct fieldName values from the searchCriteria.search array for each document. The fieldNameCombination property in the expected output Json is not an array, but a string with multiple values separated by commas. That doesn't form the valid Json. If you want to represent multiple values as an array in JSON, you should enclose them in square brackets and separate them with commas. The SELECT statement inside the ARRAY function selects the distinct fieldName property from each element of the searchCriteria.search array. The ARRAY_LENGTH function is used to calculate the length of the resulting array of distinct fieldName values. If you want to represent multiple values as a string instead of an array, you can write UDF and define it to achieve the requirement. However, the result will look like below, as the output will be a key value pair with both of the key and value in additional quotes. "fieldkeycombination": ""vendorCode" , "changeDate""