Search by array values in Firebase

Question

I use Firebase via REST API. I have following database structure:

{
  "categories" : {
    "Cat1" : {},
    "Cat2" : {},
    "Cat3" : {},
    "Cat4" : {}
  },

  "items" : {
    "item1" : {
      "categories": ["Cat1", "Cat3"]
    },
    "item2" : {
      "categories": ["Cat1", "Cat3"]
    },
    "item3" : {
      "categories": ["Cat1", "Cat2", "Cat3"]
    },
    "item4" : {
      "categories": ["Cat4"]
    }
  }
}

As you could see we have relations of type "N <-> N" between categories and items (one category could have several items and one item could be in several categories).

Now I want to get all items of Cat1 via Firebase REST API, but I can not do it.

As we know arrays are stored in the Firebase like map with integral indexes:

"categories": {
  "0": "Cat1",
  "1": "Cat2",
  "2": "Cat3",
}

So, I added ".indexOn": ["categories/*"] to Realtime Database Rules and tried to call this:
curl 'https://...firebaseio.com/...json?orderBy="categories/*"&equalTo="Cat1"'

But I got only this: { }

So, I think that regular expressions do not work in Firebase queries, because this worked:
".indexOn": ["categories/0"] in Realtime Database Rules and
curl 'https://...firebaseio.com/...json?orderBy="categories/0"&equalTo="Cat1"'

Of course, I could change the database model to something like this:

{
  "categories" : {
    "Cat1" : {},
    "Cat2" : {},
    "Cat3" : {},
    "Cat4" : {}
  },

  "items" : {
    "item1" : {},
    "item2" : {},
    "item3" : {},
    "item4" : {}
  },

  "category-items": {
    "Cat1": ["item1", "item2", "item3"],
    "Cat2": ["item3"],
    "Cat3": ["item1", "item2", "item3"]
    "Cat4": ["item4"]
  }
}

And get the category-items and iterate through the Cat1 array, but then I must to call REST API read method too many times (one REST API call for every item in the category). So, it is too expensive.

So, could anybody help me with getting all items in a category in origin database model?

UPDATE
The final model is:

{
  "categories" : {
    "Cat1" : {},
    "Cat2" : {},
    "Cat3" : {},
    "Cat4" : {}
  },

  "items" : {
    "item1" : {
      "Cat1": true,
      "Cat3": true,
    },
    "item2" : {
      "Cat1": true,
      "Cat3": true,
    },
    "item3" : {
      "Cat1": true,
      "Cat2": true,
      "Cat3": true,
    },
    "item4" : {
      "Cat4": true
    }
  }
}

Also I added

{
  rules": {
    ...
    "items": {
      ".indexOn": [ "Cat1", "Cat2", "Cat3", "Cat4" ]
    }
  }
}

to Realtime Database Rules, and REST API call is
curl 'https://...firebaseio.com/items.json?orderBy="Cat1"&equalTo=tr‌ue'

Thanks to Vladimir Gabrielyan

score 3 · Accepted Answer · answered Oct 27 '16 at 13:29

Here is the structure which I would suggest to have.

{
  "categories" : {
    "Cat1" : {
        "items": {
            "item1":{/*Some item info*/},
            "item2":{/*Some item info*/}
        }
    },
    "Cat2" : {
        "items": {
            "item3":{/*Some item info*/}
        }
    },
  },

  "items" : {
    "item1" : {
      "categories": {
          "Cat1": true,
      }
    },
    "item3" : {
      "categories": {
          "Cat2": true, 
          "Cat3": true
      }
    }
  }
}

Inside Cat1/Items/{itemId} and items/{itemId} you need to duplicate your item information, but I think that is okay.

Also see this article. https://firebase.googleblog.com/2013/04/denormalizing-your-data-is-normal.html

score 0 · Answer 2 · answered Oct 27 '16 at 14:02

0

Wow! Thank you very much! Your suggestion with replace

"item1" : {
  "categories": ["Cat1", "Cat3"]
},

to

"item1" : {
  "Cat1": true,
  "Cat3": true
},

can solve the problem, but then I will have to add every Cat to .indexOn in Realtime Database Rules, but this is not so big problem as origin problem.

But I think that

"categories" : {
  "Cat1" : {
    "items": {
        "item1":{/*Some item info*/},
        "item2":{/*Some item info*/}
    }
  },
  "Cat2" : {
    "items": {
        "item3":{/*Some item info*/}
    }
  },
}

is not a good idea in my case because then we get many spare data every time we get information about Cat (when we no need list of items, only metadata of Cat). So, I suggest following model:

{
  "categories" : {
    "Cat1" : {},
    "Cat2" : {},
    "Cat3" : {},
    "Cat4" : {}
  },

  "items" : {
    "item1" : {
      "Cat1": true,
      "Cat3": true,
    },
    "item2" : {
      "Cat1": true,
      "Cat3": true,
    },
    "item3" : {
      "Cat1": true,
      "Cat2": true,
      "Cat3": true,
    },
    "item4" : {
      "Cat4": true
    }
  }
}

answered Oct 27 '16 at 14:02

Ildar

55
1
5

okay if you do not need to duplicate items inside `categories` I would suggest the same schema for categories. `"categories" : { "Cat1" : {"items": {"item1":true,"Item2": true}} }` In that case you can get all items for `cat1` with one request, and there would no need to store index on `items/{itemId}/cat1` in order to query all items for `cat1` – Vladimir Gabrielyan Oct 27 '16 at 14:26
1

In that case I must to get full information about every `item` in separated REST API call. And this is too expensive in compare one REST API call to get all items of one category. P.S. Every `Cat` and every `item` have some extra fields. The shown data model is just simplified model. – Ildar Oct 27 '16 at 14:37
okay in that case there are two options. 1. duplicate all item data or 2. go with the way that you described below, just I guess you need add index something like this `"items": { "$itemId": { "$category": {".indexOn": ".value"} } }` – Vladimir Gabrielyan Oct 27 '16 at 14:51
Yes, you are right, in my case I added `"rules": { "items": { ".indexOn": [ "Cat1", "Cat2", "Cat3", "Cat4" ] } }` to Realtime Database Rules, because I use `orderBy` instead of `orderByValue`. And REST API call is `curl 'https://...firebaseio.com/...json?orderBy="Cat1"&equalTo=true'` – Ildar Oct 27 '16 at 16:19

Search by array values in Firebase

2 Answers2

Linked