CODEWITHSUNDEEP
arangodbaql
premnikf7
premnikf7

Reputation: 27

Arango DB Filter query for print array of value

Given the following document structure:

{
"name": [
{
  "use": "official",
  "family": "Chalmers",
  "given": [
    "Peter",
    "James"
  ]
},
{
  "use": "usual",
  "given": [
    "Jim"
  ]
},
{
  "use": "maiden",
  "family": "Windsor",
  "given": [
    "Peter",
    "James"
   ]
  }
 ]
}

Query: FOR client IN Patient FILTER client.name[*].use=='official' RETURN client.name[*].given

I have telecom and name array. I want to query to compare if name[*].use=='official' then print corresponding give array.

Expected result: "given": [ "Peter", "James" ]

Upvotes: 1

Views: 489

Answers (1)

CodeManX
CodeManX

Reputation: 11875

client.name[*].use is an array, so you need to use an array operator. It can be either of the following:

  • 'string' in doc.attribute
  • doc.attribute ANY == 'string'
  • doc.attribute ANY IN ['string']

To return just the given names from the 'official' array, you can use a subquery:

RETURN { given:
  FIRST(FOR name IN client.name FILTER name.use == 'official' LIMIT 1 RETURN name.given)
}

Alternatively, you can use an inline expression:

FOR client IN Patient
  FILTER 'official' IN client.name[*].use
  RETURN { given:
    FIRST(client.name[* FILTER CURRENT.use == 'official' LIMIT 1 RETURN CURRENT.given])
  }

Result:

[
  {
    "given": [
      "Peter",
      "James"
    ]
  }
]

In your original post, the example document and query didn't match, but assuming the following structure:

{
  "telecom": [
    {
      "use": "official",
      "value": "+1 (03) 5555 6473 82"
    },
    {
      "use": "mobile",
      "value": "+1 (252) 5555 910 920 3"
    }
  ],
  "name": [
    {
      "use": "official",
      "family": "Chalmers",
      "given": [
        "Peter",
        "James"
      ]
    },
    {
      "use": "usual",
      "given": [
        "Jim"
      ]
    },
    {
      "use": "maiden",
      "family": "Windsor",
      "given": [
        "Peter",
        "James"
      ]
    }
  ]
}

… here is a possible query:

FOR client IN Patient
  FILTER LENGTH(client.telecom[* FILTER
    CONTAINS(CURRENT.value, "(03) 5555 6473") AND
    CURRENT.use == 'official']
  )
  RETURN {
    given: client.name[* FILTER CURRENT.use == 'official' RETURN CURRENT.given]
  }

Note that client.telecom[*].value LIKE "..." causes the array of phone numbers to be cast to a string "[\"+1 (03) 5555 6473 82\",\"+1 (252) 5555 910 920 3\"]" against which the LIKE operation is run - this kind of works, but it's not ideal.

CONTAINS() is also faster than LIKE with % wildcards on both sides.

It would be possible that there are multiple 'official' elements, which might require an extra level of array nesting. Above query produces:

[
  {
    "given": [
      [
        "Peter",
        "James"
      ]
    ]
  }
]

If you know that there is only one element or restrict it to one element explicitly then you can get rid of one of the wrapping square brackets with FIRST() or FLATTEN().

Upvotes: 1

Related Questions