Group new array from matching nested array values

SQL++
1

Using Couchabse Server 7.1.5, I have a document in a collection named anomalies:

{
  "propCode": "PTEST",
  "roomNumber": "201",
  "macAddress": "ABCDEF123456",
  "date": "2021-08-04",
  "anomalies": [
    {
      "message": "More than 90% CPU used",
      "firstReportedTime": 1628101158,
      "lastReportedTime": 1628101158,
      "reportedTimesWithStay": [
        1628101158,
        1628101158,
        1628101158,
        1628101158,
        1628101158
      ]
    },
    {
      "message": "MPI is enabled",
      "firstReportedTime": 1628101158,
      "lastReportedTime": 1628101158,
      "reportedTimesWithStay": [
        1628101158,
        1628101158,
        1628101158,
        1628101158,
        1628101158
      ]
    },
    {
      "message": "MPI is enabled",
      "firstReportedTime": 1628101158,
      "lastReportedTime": 1628101158,
      "reportedTimesWithStay": [
        1628101158,
        1628101158,
        1628101158,
        1628101158,
        1628101158
      ]
    }
  ]
}

I want to match the message field inside the anomalies array. My following query will bring back the document above.

SELECT anomaliesDocument.*
FROM connectedroom.ecmp.anomalies anomaliesDocument
WHERE anomaliesDocument.date BETWEEN "2021-08-01" AND "2021-08-30"
AND ANY a IN anomaliesDocument.anomalies SATISFIES a.message = "MPI is enabled" END
ORDER BY anomaliesDocument.date;

I want to group only the anomalies with the matching message into an array and return the document with the new array:

{
  "propCode": "PTEST",
  "roomNumber": "201",
  "macAddress": "ABCDEF123456",
  "date": "2021-08-04",
  "anomalies": [
    {
      "message": "MPI is enabled",
      "firstReportedTime": 1628101158,
      "lastReportedTime": 1628101158,
      "reportedTimesWithStay": [
        1628101158,
        1628101158,
        1628101158,
        1628101158,
        1628101158
      ]
    },
    {
      "message": "MPI is enabled",
      "firstReportedTime": 1628101158,
      "lastReportedTime": 1628101158,
      "reportedTimesWithStay": [
        1628101158,
        1628101158,
        1628101158,
        1628101158,
        1628101158
      ]
    }
  ]
}

I believe I would need all 5 fields defined in the SELECT clause but what operation belongs in the SELECT clause (array_put, nest, array_agg etc?) to produce the resulting document with the new array?

2

Hi @eric_balawejder ,

SELECT ad.*, 
       ARRAY a FOR a IN ad.anomalies WHEN a.message = "MPI is enabled" END AS anomalies
FROM connectedroom.ecmp.anomalies AS ad
WHERE ad.date BETWEEN "2021-08-01" AND "2021-08-30"
AND ANY a IN ad.anomalies SATISFIES a.message = "MPI is enabled" END
ORDER BY ad.date;
1 Like
3

Thank you for the quick response. I can’t quite understand the cause of syntax error I am receiving:

[
  {
    "code": 3000,
    "msg": "syntax error - line 2, column 35, near 'OR a IN ad.anomalies', at: SATISFIES (reserved word)",
    "query": "SELECT ad.*, \n    ARRAY a FOR a IN ad.anomalies SATISFIES a.message = \"MPI is enabled\" END AS anomalies\nFROM connectedroom.ecmp.anomalies AS ad\nWHERE ad.date BETWEEN \"2021-08-01\" AND \"2021-08-30\"\nAND ANY a IN ad.anomalies SATISFIES a.message = \"MPI is enabled\" END\nORDER BY ad.date;"
  }
]
4

@eric_balawejder ,

replace SATISFIES with WHEN. updated ^^

1 Like
5

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.