How do I use SQL Parameter for CONTAINS query in Azure Cosmos DB SQL API?

Question

I have the following sql variable:

SELECT * FROM x WHERE  x.Description CONTAINS(@p1)

I have configured a SQL Parameter with name @p1 and a valid string i.e. "foo" and added it to the SqlParameterCollection collection variable with the following:

var sqlQuery = new SqlQuerySpec(sql, collection);

I then invoke the following query.

var query = _documentClient.CreateDocumentQuery<T>(GetDocumentCollectionUri<T>(graphRequestContext), sqlQuery, options).AsDocumentQuery();

I am getting the following error:

Message: Microsoft.Azure.Documents.DocumentClientException : Message: {"errors":[{"severity":"Error","location":{"start":37,"end":45},"code":"SC1001","message":"Syntax error, incorrect syntax near 'CONTAINS'."}]} ActivityId: <Removed for privacy reasons>, Microsoft.Azure.Documents.Common/2.2.0.0, Windows/10.0.17763 documentdb-netcore-sdk/2.2.2

Per documentation (https://docs.microsoft.com/en-us/azure/cosmos-db/sql-api-query-reference), it seems to be looking for a string expression. Thus, I am looking for confirmation on this, if this is in fact the case.

Jay Gong Jay Gong · Accepted Answer · 2019-03-18T06:20:52

It seems that you use CONTAINS in wrong syntax.Please get an idea of the correct syntax.

Please refer to below code,it works for me.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using System;

namespace DocumentDB.TestClass
{
    class CreateBySqlParam
    {
        private static readonly string endpointUrl = "https://***.documents.azure.com:443/";
        private static readonly string authorizationKey = "***";
        private static readonly string databaseId = "db";
        private static readonly string collectionId = "***";

        private static DocumentClient client;

        public static async void QueryTest()

        {
            client = new DocumentClient(new Uri(endpointUrl), authorizationKey);
            var uri = UriFactory.CreateDocumentCollectionUri(databaseId, collectionId);

            IQueryable<Pojo> queryable = client.CreateDocumentQuery<Pojo>(
                                                uri,
                                                new SqlQuerySpec
                                                {
                                                    QueryText = "SELECT c.id,c.name FROM c WHERE contains(c.name,@param)",
                                                    Parameters = new SqlParameterCollection()
                                                    {
                                                        new SqlParameter("@param", "n")
                                                    }

                                                }
            );

            foreach (Pojo p in queryable)
            {
                Console.WriteLine("\nRead {0}", p);
            }
        }
    }

    class Pojo : Document
    {
        public string id { get; set; }
        public string name { get; set; }
    }

}

Output:

How do I use SQL Parameter for CONTAINS query in Azure Cosmos DB SQL API?

1 Answers