CODEWITHSUNDEEP
azureazure-cosmosdb
King Yew Fong
King Yew Fong

Reputation: 1

Select certain value in Array in Documentdb

Let's say I have the following collection:

 [{"id": "1123",
   "columns": [
         { "columnId": "1", "value":3},
         { "columnId": "2", "value":2 },
         { "columnId": "3", "value":7 }
         ]
   },
   {"id": "1124",
   "columns": [
         { "columnId": "1", "value":5},
         { "columnId": "2", "value":6 },
         { "columnId": "3", "value":2 }
         ]
   },
]

I want to query as following result:

 [{"id": "1123",
   "columns": [
         { "columnId": "1", "value":3},
         { "columnId": "2", "value":2},
         ]
   },
   {"id": "1124",
   "columns": [
         { "columnId": "1", "value":5},
         { "columnId": "2", "value":6},
         ]
   },
]

I have tried this query

SELECT c.id, [f] FROM c JOIN f IN c.columns WHERE f.columnId IN ('1','2')

But its output looks like this which is not what I want:

[
  {
    "id": "1123",
    "$1": [{"columnId": "1", "value": 3}]
  },
  {
    "id": "1123",
    "$1": [{"columnId": "2", "value": 2}]
  },
  {
    "id": "1124",
    "$1": [{"columnId": "1", "value": 5}]
  },
  {
    "id": "1124",
    "$1": [{"columnId": "2", "value": 6}]
  }
]

Upvotes: 0

Views: 653

Answers (1)

Larry Maccherone
Larry Maccherone

Reputation: 9533

Here is a user defined function (UDF) that will expose JavaScript's Array.slice() method:

function(array, begin, end) {
  return array.slice(begin, end);
}

If you use it in a query against your example docs like this, you get the output are asking for:

SELECT c.id, udf.slice(c.columns, 0, 1) as columns FROM c

You could also just use the slice operator from within a JavaScript language-integrated query but that's only available in the server-side SDK for use in sprocs or triggers.

In general, I'm not a fan of DocumentDB's self join functionality for formatting the output. IMHO, UDFs are easier to write and the queries that consume them are easier to understand.

The only down side is the added burden of getting your UDFs into each of your collections, but I'm the author of the node.js package documentdb-utils where I have functionality to automatically load all sprocs found in a directory to every collection in the list you provide. I'm adding the same for UDFs now.

Note, the documentation for documentdb-utils is a bit behind the major refactoring I did in the last few days but the tests show you how to use most of its functionality. I'll get it in good shape today and add functionality to load all UDFs just like it loads all sprocs.

Upvotes: 1

Related Questions