Query AQL syntax required for following JSON structure

Question

I have a JSON structure like this

{
  "Items": {
     "Apple": {
       "Type": 2,
       "keyVal": "6044e3a3-c064-4171-927c-2440e2f65660"
     },
     "Lemons": {
       "Type": 1,
       "keyVal": "79c45f4d-4f62-4c8e-8de1-79e04fc9b95d"
     }
  },
  "Species": 0,
  "Name": "Test Fruit",
  "Description": "Test Des",
  "Creator": "xyz",
  "SKey" : "123"
}

This is present in a collection named Fruits.

Query: I am trying to write and AQL query to find the SKey where KeyVal value in Items is some value.

I am traditionally used to the SQL syntax but this is the first time I am venturing into the AQL(Arango DB).

Any help with the Syntax is appretiated

Answer 1

The basics of AQL are explained here really well: https://docs.arangodb.com/3.11/aql/

FOR item IN Items FILTER item.keyVal == "someValue" RETURN item

Would be your minimal SQL SELECT ... WHERE statement.

BTW: There is a comparative introduction to be found here:
https://arangodb.com/why-arangodb/sql-aql-comparison/

A good way to learn AQL is to try small pieces of code an return the result for inspection, to gradually create more complex queries.

For example, let's return one of the nested keyVal values:

FOR doc IN Fruits
  RETURN doc.Items.Apple.keyVal
  // "6044e3a3-c064-4171-927c-2440e2f65660"

To filter by Apple keyVal and return SKey, you can do:

FOR doc IN Fruits
  FILTER doc.Items.Apple.keyVal == "6044e3a3-c064-4171-927c-2440e2f65660"
  RETURN doc.SKey
  // "123"

You can return both keyVal values too:

FOR doc IN Fruits
  RETURN [
    doc.Items.Apple.keyVal,
    doc.Items.Lemons.keyVal
  ]
  // [
  //   "6044e3a3-c064-4171-927c-2440e2f65660",
  //   "79c45f4d-4f62-4c8e-8de1-79e04fc9b95d"
  // ]

To return SKey if either is equal to some value, try this:

FOR doc IN Fruits
  FILTER "79c45f4d-4f62-4c8e-8de1-79e04fc9b95d" IN [
    doc.Items.Apple.keyVal,
    doc.Items.Lemons.keyVal
  ]
  RETURN doc.SKey

Note: IN is used here as array operator, like is {value} contained in {array}.

To return all keyVal values hardcoding the attribute paths, you can make use of the ATTRIBUTES() AQL function:

FOR doc IN Fruits
  FOR attr IN ATTRIBUTES(doc.Items)
    RETURN doc.Items[attr].keyVal

To return SKey if any of the nested keyVal values match, we can do:

FOR doc IN Fruits
  LET keyVals = (FOR attr IN ATTRIBUTES(doc.Items)
    RETURN doc.Items[attr].keyVal
  )
  FILTER "6044e3a3-c064-4171-927c-2440e2f65660" IN keyVals
  RETURN doc.SKey

Note: this uses a subquery to capture the intermediate result.

To test if all specified values are contained, you could do:

LET ids = [
  "79c45f4d-4f62-4c8e-8de1-79e04fc9b95d",
  "6044e3a3-c064-4171-927c-2440e2f65660"
]
FOR doc IN Fruits
  LET keyVals = (FOR attr IN ATTRIBUTES(doc.Items)
    RETURN doc.Items[attr].keyVal
  )
  FILTER ids ALL IN keyVals
  RETURN doc.SKey

ALL IN is an array comparison operator.

Note that it would require a change to your data model if you wanted to use indexes without hardcoding the attributes paths, and also different queries.