AQL nested array values

Question

I'm fairly new to ArangoDB and AQL and my question pertains to nested array values. I do not know if there is a way this can be done (or even if my query is efficiently written)

To keep this short, I have 2 collections. One for "Physicians" and one for "Indications". Each time my patient sees a doctor for a consult, the list of his/her symptoms are recorded in the Indications collection. However, a previous diagnosis may have been made by another doctor. In this case, my AQL syntax looks like this:

FOR p in Patients
FILTER p._key=="cad010117"
RETURN {
   "name" : CONCAT(p.pfname, " ", p.plastname),
   "consultations" : (
       FOR i in Indications
       FILTER i.pat_id == p._key
          FOR d in Physicians
          FILTER i.ind_consul_doc == d._key

          RETURN {
              "Doctor" : CONCAT(d.dtitle, " ", d.dfirstname, " ", d.dlastname),
              "Consult Date" : i.ind_consul_dt,
              "Symptoms" : i.symptoms[*],
              "Past Diagnoses" : i.diagnosis[* RETURN {
                      "Condition" : CURRENT.diag, 
                      "Diagnosed on" : CURRENT.diag_dt, 
                      "Diagnosed by" : CURRENT.diag_doc
                  }
              ]
          }
   )

My corresponding results:

[
  {
    "name": "Alfred Jeffries",
    "consultations": [
      {
        "Doctor": "Dr Anthony Garcia",
        "Consult Date": "22nd Jun 2016",
        "Symptoms": [
          {
            "type": "hyperpyrexia",
            "details": "Temperature: 101",
            "duration": "3d"
          },
          {
            "type": "anxiety",
            "details": "Severe",
            "duration": "1w"
          },
          {
            "type": "mania",
            "details": "Moderate",
            "duration": "1w"
          },
          {
            "type": "diaphoresis",
            "details": "Increased sweating during the day only",
            "duration": "3d"
          }
        ],
        "Past Diagnoses": [
          {
            "Condition": "Bipolar Disorder",
            "Diagnosed on": "6th Jul 2016",
            "Diagnosed by": "dc666555"
          }
        ]
      }
    ]

My question is, for the Past Diagnoses array returned, the diag_doc field, which is a value inside the diagnosis array in my "Indications" collection, corresponds with a _key value in the "Physicians" collection of the doctor who made the diagnosis. What would be the best way to return full details of the diag_doc using AQL? Thank you in advance.

PS: I'm using ArangoDB 3.1.12

Christian · Accepted Answer · 2017-03-22 09:20:51Z

1

You can use a sub-query to retrieve the data from the Physicians collection:

"Past Diagnoses": (FOR d IN i.diagnosis RETURN 
  {
    "Condition" : d.diag, 
    "Diagnosed on" : d.diag_dt,
    "Diagnosed by" : FIRST(FOR doc IN Physicians FILTER doc._key == d.diag_doc RETURN doc)
  }
)

answered Mar 22, 2017 at 9:20

Christian

3152 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex J. Over a year ago

Thanks a ton! Worked like a charm :)

Collectives™ on Stack Overflow

AQL nested array values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related