ArangoDB search by string field

Question

I want to do a simple search by text in a specific field of a specific collection in ArangonDB. Something like this ( in SQL ):

SELECT * FROM procedures WHERE procedures.name LIKE '%hemogram%'

I need to search in a string field of object ( document? ) that is part of an array that is a field of my actuall document:

[
  {
    "name": "Unimed",
    "procedures": [
      {
        "type": "Exames",
        "name": "Endoscopia"
      },
      {
        "type": "Exame",
        "name": "Hemograma"
      }
    ]
  }
]

I want to retrieve, for example, all procedures that name likes a "string", searching in all documents of my clinics collection.

I've been reading about fulltext indexes but I couldn't understand how to create or how to use them.

Any help would be great!

EDIT

I almost got what I wanted. My problem is now return just the information I want.

FOR clinic IN clinics
  FILTER LIKE(clinic.procedures[*].name, '%hemogram%', true)
  RETURN{
   clinic_name: clinic.name,
   procedure: clinic.procedures
  }

This returns to me all the procedures in a given clinic ( procedures is an array inside a clinic ) and not only the procedure which the field name is 'LIKE' my search string. How can I achieve this?

score 1 · Answer 1 · answered Jul 31 '15 at 09:31

1

ArangoDB does matching in a similar way as SQL. You however have to address the field you want to execute the LIKE on: (You have to have an object as toplevel, with the mandatory attributes _key etc.)

FOR document IN myCollection
  FILTER LIKE(document.procedures.name, '%hemogram%')
  RETURN document;

answered Jul 31 '15 at 09:31

dothebart

5,972
16
40

The idea of my query is that, but the statement you gave me is returning an error. The 'procedures' is an array field of my document (Clinics), that have objects inside it, and those objects have some fields, which one of them is the string field which I want to query. – Pavarine Jul 31 '15 at 16:23

score 0 · Answer 2 · answered Aug 08 '15 at 16:21

You might wanna rethink your data model. Your documents store two different types of entities, clinics and procedures.

You could store clinics in a clinics collection, and their procedures in a procedures collection (with optional de-duplication enabled by the separation into two collections). Then link clinics to the procedures, either by an array of procedure _ids in clinic records, or by using an edge collection to link clinic and procedure documents with edges.

If you want to keep the current data model, use the following AQL query:

FOR clinic IN clinics
    FOR proc IN clinic.procedures
        FILTER LIKE(proc.name, "%hemo%", true)
        RETURN MERGE(
            UNSET(clinic, "procedures"),
            {procedure: proc}
        )

It's not possible to use the abbreviated syntax ([*] operator) in your case.

The approach is to remove the procedures attribute from the document, add a new attribute procedure and the matched procedure object as value. The problem is, that multiple results will be returned if LIKE() found more than a single procedure. You could LIMIT this to 1 below FILTER, but that may not be desired.

To return a single result instead, with procedures attribute reduced to matching procedures, a sub-query is required:

FOR clinic IN clinics
    LET p = (
        FOR proc IN clinic.procedures
            FILTER LIKE(proc.name, "%hemo%", true)
            RETURN proc
    )

    RETURN MERGE(
        clinic,
        {procedures: p}
    )

ArangoDB search by string field

2 Answers2