DocumentDB get min of subarray

Question

my json data:

[
    {
    "Code": "GB-00001",
    "BasicInformation": {
        "WGS84Longitude": -4.670000,
        "WGS84Latitude": 50.340000
     },
     "Availability": [{
            "ArrivalDate": "2017-04-21",
            "Price": 689
        },
        {
            "ArrivalDate": "2017-04-28",
            "Price": 1341
        }
     ]},
    {
    "Code": "GB-00002",
    "BasicInformation": {
        "WGS84Longitude": -4.680000,
        "WGS84Latitude": 50.350000
     },
     "Availability": [{
            "ArrivalDate": "2017-04-21",
            "Price": 659
        },
        {
            "ArrivalDate": "2017-04-28",
            "Price": 1440
        }
     ]}
}]

I'd like the result to be like:

[
{
    "HouseCode": "GB-00001",
    "Country": "GB",
    "location": {
        "type": "Point",
        "coordinates": [
            50.340000,
            -4.670000
        ]
    }, "lowestPrice": 689
},
{
    "HouseCode": "GB-00002",
    "Country": "GB",
    "location": {
        "type": "Point",
        "coordinates": [
            50.350000,
            -4.680000
        ]
    }, "lowestPrice" : 659
}

My problem is: how to use the min(c.Availability.Price)

This is my current query with the lat lng convert to point, but no idea how to get the minimum/lowest price.

SELECT c.Code, c.BasicInformation.Country , 
    {"type":"Point","coordinates": [c.BasicInformation.Latitude, c.BasicInformation.Longitude]} as location
FROM c

already tried with Join c.Availability a and , min(a.Price)

edit perhaps I am too early? https://feedback.azure.com/forums/263030-documentdb/suggestions/18561901-add-group-by-support-for-aggregate-functions found that url in https://stackoverflow.com/a/42697673/169714

score 2 · Accepted Answer · answered Apr 20 '17 at 14:27

2

This is a pretty close to ideal situation for a user defined function (UDF).

Here is one that should do the trick:

function lowestPrice(availability) {
  var i, len, lowest, row;
  lowest = 2e308;
  for (i = 0, len = availability.length; i < len; i++) {
    row = availability[i];
    lowest = Math.min(lowest, row.Price);
  }
  return lowest;
};

You call it like this:

SELECT 
  c.Code, 
  c.BasicInformation.Country, 
  {"type":"Point","coordinates": [
    c.BasicInformation.Latitude, c.BasicInformation.Longitude
  ]} as location,
  udf.lowestPrice(c.Availability) as lowestPrice
FROM c

answered Apr 20 '17 at 14:27

Larry Maccherone

9,393
3
27
43

So I can't use min(a.Price) because of the lack of grouping when using an aggregate function and need to create a user defined function? I have never used a udf before so I will look into it and will get back to this. Thank you for your answer. edit: crashed http://imgur.com/a/hxoJ9 – JP Hellemons Apr 20 '17 at 14:46
The screen shot is not in English – Larry Maccherone Apr 20 '17 at 14:52
it is just a regular Windows message. DocumentDB.GatewayService.exe has stopped working. etc. – JP Hellemons Apr 21 '17 at 07:04

score 1 · Answer 2 · answered Apr 25 '17 at 03:36

AFAIK, you could only use UDF to achieve your requirement for now. Also, I have checked the code provided by Larry Maccherone, and it could both work on Azure DocumentDB service and my DocumentDB Emulator (version 1.11.136.2) as follows:

DocumentDB.GatewayService.exe has stopped working

For DocumentDB.GatewayService crash, I assumed that you need to collect the dump files and attach them with an email to askdocdb@microsoft.com. For more details, you could refer to DocumentDB Emulator troubleshooting.

Thank you! I have mailed them. – JP Hellemons Apr 25 '17 at 10:19 — JP Hellemons, Apr 25 '17 at 10:19

DocumentDB get min of subarray

2 Answers2