Search code examples
sqlnode.jssplitincludeazure-cosmosdb

SQL query to use .split() and .include(


I have the below SQL query for my Azure Cosmos DB

SELECT distinct c.id1,stuff.id2,e.id3 FROM c
join stuff in c.stuff
join d in stuff.a.b.c.d
join e in  d.e
where ARRAY_CONTAINS(e.classes,{name:"${searchWord}"},true)

The issue I am having is that this only returns exact name matches.

What I am trying to do is find any name that includes the searchWord similar to classes.name.split(' ').includes(searchWord) - for example return "green cup" when searchWord == cup. In Node it would be:

   var splice = name.split(' ');
   if(splice.includes(searchWord))

Is someone able to help clear up where I am going wrong?


Solution

  • I assumed you that you want to implement a function similar to LIKE in Cosmos DB SQL API. The keyword for LIKE is CONTAINS here. If you had a document with a firstName property and you wanted to filter on the name 'bob' you would use it in a query this way:

    "SELECT * FROM c WHERE CONTAINS(c.firstName, 'bob')"
    

    Please try below sql:

    SELECT distinct c.id1,stuff.id2,e.id3 FROM c
    join stuff in c.stuff
    join d in stuff.a.b.c.d
    join e in  d.e
    join class in e.classes
    where contains(class.name,"trees")
    

    Updated Answer

    I got your idea. But sorry, there is no such keyword or function like you said in cosmos db currently. So, I don't think you can rely entirely on sql. You can try the combination of sql and code, which eliminates a lot of loops and reduces the pressure on the code, which can also meet your individual needs.

    In addition, you could submit feedback to ask Azure Cosmos DB Team to support such function in the future.