2 Replies Latest reply: Aug 21, 2013 5:14 AM by user8740364 RSS

    Search on value fields in NoSQL?

    user8740364

      Am I right in thinking that the Oracle NoSQL API does not support querying on object values?

       

      For example, if I am storing information about vehicles, I might choose to have the following components for the Key:

       

      Major components:

      • License Number
      • Manufacturer
      • Model
      • Chassis Number

       

      Minor components

        • Colour
        • Trim Level
        • Stereo Model

       

      The Value might then also contain:

      • Registered Keeper
      • Registered Keeper’s address
      • Mileage at last service
      • Next service due
      • MOT Certificate Number
      • MOT Certificate Expiry Date

       

      Given this key/value structure, my understanding is that if I want to find all the cars within the county of Yorkshire that have an MOT Certificate that expires in the next month, I would need to:

      1. Retrieve all the vehicle record values from the store.
      2. Check each value to see if the Registered Keeper has an address in the county of Yorkshire
      3. If so, check to see if the MOT Certificate expires in the next month.

       

      Is this correct, or is there another way to search the fields of the value objects directly?

       

      Also, would this be a good candidate use case for Index Views?

       

      Thanks,

       

      Justin

        • 1. Re: Search on value fields in NoSQL?
          Gmfeinberg-Oracle

          Justin,

           

          You are correct that there is no way to search by values within a record unless those values are encoded into the record's key in some manner.  In the current release you can either model the keys so that you can group records as you need them or you can use the concept of "Index Views" described in the documentation.  A future release may have the ability to index record data directly.

           

          The Index Views documentation describes ways to create your own indexes on data.  It works best if your "primary" data record has a single unique key that can be referenced from one or more "secondary" index views.  I don't know how definitive your model is, and why you made your keys so modular, but it sounds to me like it might be simply modeled as a single record, keyed by License Number.  For example the major key might be /LN/license-number with no minor component at all.  The records keyed by this would contain the rest of the information you list.

           

          You'd then create "secondary" views for each property you'd like to search on.  For example if you wanted to "index" on MOT expiry date you could create this view with key-only records of the format /MOT/<year>/<month>/<day>/-/<license-number>.   This puts all expirations for a given day in the same partition making them efficient to retrieve.  The major/minor split could be however you'd like to group.  As mentioned in the documentation such views are probably not transactional with respect to their related primary records so applications must account for that.

           

          Similarly you can create other index views based on other properties.  A "join" would need to be done in code where you'd look at a combination of index views and data.

           

          One thing I can say about future plans is that if you wish to use indexes that the system provides over data you define today records should be defined in terms of an Avro schema.

           

          Regards,

          George

          • 2. Re: Search on value fields in NoSQL?
            user8740364

            Hi George,

             

            Thanks for the reply, and confirming that this might be a good case for index views.

             

            What you said about Avro is interesting.  I am using an Avro schema, do you know if it will be possible to query on values of records that are defined using Avro in the future?

             

            The key structure isn't set in stone (is there some advice / best practice you could point me to on modelling key structures?).

             

            Thanks,

             

            Justin