How to perform sql "LIKE" operation on firebase?

Question

I am using firebase for data storage. The data structure is like this:

products:{
   product1:{
      name:"chocolate",
   }
   product2:{
      name:"chochocho",
   }
}

I want to perform an auto complete operation for this data, and normally i write the query like this:

"select name from PRODUCTS where productname LIKE '%" + keyword + "%'";

So, for my situation, for example, if user types "cho", i need to bring both "chocolate" and "chochocho" as result. I thought about bringing all data under "products" block, and then do the query at the client, but this may need a lot of memory for a big database. So, how can i perform sql LIKE operation?

Thanks

Answer 1

Update: With the release of Cloud Functions for Firebase, there's another elegant way to do this as well by linking Firebase to Algolia via Functions. The tradeoff here is that the Functions/Algolia is pretty much zero maintenance, but probably at increased cost over roll-your-own in Node.

There are no content searches in Firebase at present. Many of the more common search scenarios, such as searching by attribute will be baked into Firebase as the API continues to expand.

In the meantime, it's certainly possible to grow your own. However, searching is a vast topic (think creating a real-time data store vast), greatly underestimated, and a critical feature of your application--not one you want to ad hoc or even depend on someone like Firebase to provide on your behalf. So it's typically simpler to employ a scalable third party tool to handle indexing, searching, tag/pattern matching, fuzzy logic, weighted rankings, et al.

The Firebase blog features a blog post on indexing with ElasticSearch which outlines a straightforward approach to integrating a quick, but extremely powerful, search engine into your Firebase backend.

Essentially, it's done in two steps. Monitor the data and index it:

var Firebase = require('firebase');
var ElasticClient = require('elasticsearchclient')

// initialize our ElasticSearch API
var client = new ElasticClient({ host: 'localhost', port: 9200 });

// listen for changes to Firebase data
var fb = new Firebase('<INSTANCE>.firebaseio.com/widgets');
fb.on('child_added',   createOrUpdateIndex);
fb.on('child_changed', createOrUpdateIndex);
fb.on('child_removed', removeIndex);

function createOrUpdateIndex(snap) {
   client.index(this.index, this.type, snap.val(), snap.name())
     .on('data', function(data) { console.log('indexed ', snap.name()); })
     .on('error', function(err) { /* handle errors */ });
}

function removeIndex(snap) {
   client.deleteDocument(this.index, this.type, snap.name(), function(error, data) {
      if( error ) console.error('failed to delete', snap.name(), error);
      else console.log('deleted', snap.name());
   });
}

Query the index when you want to do a search:

<script src="elastic.min.js"></script>
 <script src="elastic-jquery-client.min.js"></script>
 <script>
    ejs.client = ejs.jQueryClient('http://localhost:9200');
    client.search({
      index: 'firebase',
      type: 'widget',
      body: ejs.Request().query(ejs.MatchQuery('title', 'foo'))
    }, function (error, response) {
       // handle response
    });
 </script>

There's an example, and a third party lib to simplify integration, here.

Answer 2

I believe you can do :

admin
.database()
.ref('/vals')
.orderByChild('name')
.startAt('cho')
.endAt("cho\uf8ff")
.once('value')
.then(c => res.send(c.val()));

this will find vals whose name are starting with cho.

source

Answer 3

The elastic search solution basically binds to add set del and offers a get by wich you can accomplish text searches. It then saves the contents in mongodb.

While I love and reccomand elastic search for the maturity of the project, the same can be done without another server, using only the firebase database. That's what I mean: (https://github.com/metaschema/oxyzen)

for the indexing part basically the function:

1. JSON stringifies a document.
2. removes all the property names and JSON to leave only the data (regex).
3. removes all xml tags (therefore also html) and attributes (remember old guidance, "data should not be in xml attributes") to leave only the pure text if xml or html was present.
4. removes all special chars and substitute with space (regex)
5. substitutes all instances of multiple spaces with one space (regex)
6. splits to spaces and cycles:
7. for each word adds refs to the document in some index structure in your db tha basically contains childs named with words with childs named with an escaped version of "ref/inthedatabase/dockey"
8. then inserts the document as a normal firebase application would do

in the oxyzen implementation, subsequent updates of the document ACTUALLY reads the index and updates it, removing the words that don't match anymore, and adding the new ones.

subsequent searches of words can directly find documents in the words child. multiple words searches are implemented using hits

Answer 4

SQL"LIKE" operation on firebase is possible

let node = await db.ref('yourPath').orderByChild('yourKey').startAt('!').endAt('SUBSTRING\uf8ff').once('value');

Answer 5

This query work for me, it look like the below statement in MySQL

select * from StoreAds where University Like %ps%;

query = database.getReference().child("StoreAds").orderByChild("University").startAt("ps").endAt("\uf8ff");