Dariusz Majchrzak Dariusz Majchrzak - 2 years ago 82
JSON Question

Filtering json similar to mysql "like" operator

I wanna filter my json, and I try to simulate simple mysql operator "like" with wildcard - "%", but with one... some kind of "trick".

We have some items:

var items =
{id: 1,name: "Breaking Bad"},
{id: 2,name : "Bates Motel"}

When I use PHP with MySQL, I replace every spaces in users input using '%'. So, when user types "b ba", query looks like:

$this->db->query("SELECT * FROM items WHERE name LIKE '%b%ba% ");

User gets item: "Breaking Bad". I wanna simulate the same behaviour with javascript and json.

My closest implementation looks like:

this.search = function() {
var query = $.trim($('.player-search').val())
query = query.toUpperCase()

$.grep(that.items, function(v) {
if(that.filter(v.name,query) === true) {
} else {

this.filter = function(word,query) {
var tmp = query.toUpperCase().split(" "),
n = tmp.length,
i = 0

for(var k in tmp) {
word.toUpperCase().indexOf(tmp[k]) >= 0 && ++i

return (i == n) ? true : false

That way has one horrible bug, when user types "b ba", users gets "Breaking Bad" and "Bates Motel", becouse word "Bates" has "b" and "ba".

Before I try to write it again, I wanna ask are there better/smarter ways to filter json as I want? It may be that I'm trying to invent the wheel again.

Answer Source

You can use $.grep(), String.prototype.match(), Array.prototype.every(), String.prototype.slice()

var items = [{
  id: 1,
  name: "Breaking Bad"
}, {
  id: 2,
  name: "Bates Motel"
var query = "b ba";
var res = $.grep(items, function(v) {
  // if space character included in query match first word to 
  // first word of `v.name`, last word to last word in `v.name`
  if (/\s/.test(query)) {
    var curr = v.name.match(/\w+/g).map(function(_q) {
      return _q.toLowerCase()
    var q = query.match(/\w+/g);
    if (q.every(function(val, index) {
      return val === curr[index].slice(0, val.length)
    })) return true
  } else {
    if (q.toLowerCase() === v.name.toLowerCase().slice(0, q.length)) {
      return true

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js">

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download