Search code examples
azureazure-cosmosdbazure-data-factory

Unable to get scalar value of a query on cosmos db in azure data factory

I am trying to get the count of all records present in cosmos db in a lookup activity of azure data factory. I need this value to do a comparison with other value activity outputs.

The query I used is SELECT VALUE count(1) from c

When I try to preview the data after inserting this query I get an error saying 

One or more errors occurred. Unable to cast object of type  
 'Newtonsoft.Json.Linq.JValue' to type 'Newtonsoft.Json.Linq.JObject'

as shown in the below image: snapshot of my azure lookup activity settings

Could someone help me in resolving this error and if this is the limitation of azure data factory how can I get the count of all the rows of the cosmos db document using some other ways inside azure data factory?

Solution

  • I reproduce your issue on my side exactly.

    enter image description here

    I think the count result can't be mapped as normal JsonObject. As workaround,i think you could just use Azure Function Activity(Inside Azure Function method ,you could use SDK to execute any sql as you want) to output your desired result: {"number":10}.Then bind the Azure Function Activity with other activities in ADF.

    Here is contradiction right now:

    The query sql outputs a scalar array,not other things like jsonObject,or even jsonstring.

    enter image description here

    However, ADF Look Up Activity only accepts JObject,not JValue. I can't use any convert built-in function here because the query sql need to be produced with correct syntax anyway. I already submitted a ticket to MS support team,but get no luck with this limitation.

    enter image description here

    I also tried select count(1) as num from c which works in the cosmos db portal. But it still has limitation because the sql crosses partitions.

    enter image description here

    So,all i can do here is trying to explain the root cause of issue,but can't change the product behaviours.

    2 rough ideas:

    1.Try no-partitioned collection to execute above sql to produce json output.

    2.If the count is not large,try to query columns from db and loop the result with ForEach Activity.