Filter per item properties in a nested array

[gmourier]

Today filters are applied to the whole document, so if I want to filter for a document that contains a specific nested item, I am forced to denormalize my data to perform it, which is a tedious workaround and only sometimes possible.

Example

[
   {
      "id":1082,
      "name":"Thomas",
      "experiences":[
         {
            "name":"Airbus & Inc",
            "ongoing":0
         },
         {
            "name":"Apple",
            "ongoing":1
         }
      ]
   },
   {
      "id":1083,
      "name":"Guillaume",
      "experiences":[
         {
            "name":"Airbus & Inc",
            "ongoing":1
         },
         {
            "name":"Apple",
            "ongoing":1
         }
      ]
   }
]

I want a way to retrieve only the documents where an experience object match experiences.name CONTAINS 'Airbus' AND experiences.ongoing = 1; in that case, the document 1083. Meilisearch returns both today.

Workaround

I could inverse the way the relationship is mapped in the document. Each experience is a document; I duplicate the person's information in all related experiences.

[
   {
      "id":1,
      "name":"Airbus & Inc",
      "ongoing":0,
      "person":{
         "id":1082,
         "name":"Thomas"
      }
   },
   {
      "id":2,
      "name":"Apple",
      "ongoing":1,
      "person":{
         "id":1082,
         "name":"Thomas"
      }
   },
   {
      "id":3,
      "name":"Airbus & Inc",
      "ongoing":1,
      "person":{
         "id":1083,
         "name":"Guillaume"
      }
   },
   {
      "id":4,
      "name":"Apple",
      "ongoing":1,
      "person":{
         "id":1083,
         "name":"Guillaume"
      }
   }
]

It could be great to have a way to express a filter that is applied per nested item to match a document.

Mentioned by @Nakusu here

[gaurav8298]

I agree with @gmourier , Infact in my use case, I run a global e commerce website where I have different prices for each country. An exmaple of that is below:

[{ "country": "default", "price": "10.00" }, { "country": "DZ", "price": "11.30" }, { "country": "BS", "price": "11.30" }, { "country": "GB", "price": "12.00" }]

Toda, if I add a filter to find a document with country DZ and price 11.30, meilisearch will return all documents that has DZ as a country and at the same time there is any country having a price 11.30

Rather I want just to match values within the same object.

Having facets on this will be a great thing as well.

[oskobri]

Hi,

I'm facing the same problem.

{
  "id": 0,
  "patient_name": "Imogen Temult",
  "appointments": [
    {
      "date": "2022-01-01",
      "doctor": "Jester Lavorre",
      "ward": "psychiatry"
    },
    {
      "date": "2019-01-01",
      "doctor": "Dorian Storm"
    }
  ]
}

If I filter on "appointments.doctor = Jester Lavorre and appointments.date = 2019-01-01", it will return the document but it should not because Jester Lavorre appointment is for the date 2022-01-01.

I had the same problem with another index but filterable attributes are only ids so i got around the problem by concatenating the ids into a single string. Example:

{
  "leagues_played": [
    26,
    273,
    4,
    3,
    29
  ],
  "leagues_played_by_season": [
    "62.26",
    "64.26",
    "66.26",
    "68.26",
    "78.273",
    "80.4",
    "82.3",
    "84.29",
    "86.3"
  ],
}

I wanted to filter by leagues played and seasons so i concatenated league id and season id but it only work if it's ids and if there aren't many items.

[Nakusu]

@gmourier Thanks for your return. I'm making it actually :)

[grabelle]

Hi all,
I'm facing the same problem. My data are as follows. If I try to filter and ask for a 'couleur rouge' and 'taille M' AND 'location Yverdon'... my search returns the document. But that's incorrect. It's a very basic situation. Unfortunately, I will have to use Elasticsearch to solve this. Any suggestions?

My documents.

[
  {
    "id": 1,
    "nom": "T-shirt confort",
    "marque": "\u00c9l\u00e9gancePure",
    "prix": 22.71,
    "declinaisons": [
      {
        "couleur": "rouge",
        "tailles": [
          {
            "taille": "S",
            "stock": 6,
            "locations": [
              "Bern",
              "Lausanne",
              "Gen\u00e8ve",
              "Yverdon",
              "Conthey"
            ]
          },
          {
            "taille": "M",
            "stock": 15,
            "locations": [
              "Yverdon"
            ]
          },
          {
            "taille": "L",
            "stock": 15,
            "locations": [
              "Bern",
              "Conthey"
            ]
          },
          {
            "taille": "XL",
            "stock": 11,
            "locations": [
              "Lausanne",
              "Bern",
              "Conthey",
              "Gen\u00e8ve"
            ]
          }
        ]
      },
      {
        "couleur": "vert",
        "tailles": [
          {
            "taille": "S",
            "stock": 19,
            "locations": [
              "Conthey",
              "Yverdon",
              "Lausanne"
            ]
          },
          {
            "taille": "M",
            "stock": 18,
            "locations": [
              "Lausanne",
              "Bern",
              "Gen\u00e8ve"
            ]
          },
          {
            "taille": "L",
            "stock": 3,
            "locations": [
              "Yverdon",
              "Gen\u00e8ve",
              "Lausanne",
              "Bern",
              "Conthey"
            ]
          },
          {
            "taille": "XL",
            "stock": 7,
            "locations": [
              "Yverdon",
              "Conthey",
              "Lausanne",
              "Bern",
              "Gen\u00e8ve"
            ]
          }
        ]
      },
      {
        "couleur": "bleu",
        "tailles": [
          {
            "taille": "S",
            "stock": 14,
            "locations": [
              "Lausanne",
              "Yverdon",
              "Bern"
            ]
          },
          {
            "taille": "M",
            "stock": 5,
            "locations": [
              "Lausanne",
              "Yverdon",
              "Bern",
              "Gen\u00e8ve",
              "Conthey"
            ]
          },
          {
            "taille": "L",
            "stock": 18,
            "locations": [
              "Conthey",
              "Bern"
            ]
          },
          {
            "taille": "XL",
            "stock": 10,
            "locations": [
              "Yverdon",
              "Conthey",
              "Lausanne"
            ]
          }
        ]
      }
    ],
    "description": "Sweat outdoor pour tous les jours.",
    "images": [
      "url3.jpg"
    ],
    "categorie": "v\u00eatements",
    "tags": [
      "\u00e9t\u00e9",
      "coton",
      "unisexe"
    ]
  }
]

[wiperawa]

Facing the same issue when trying to implement filter on products list by min and max price per distributor, when product have different prices for each distributor, meilisearch gives result that did not actually match filter..
any update on that ?

[irevoire]

Hey, there we didn't implement a better solution than the one highlighted @gmourier in this discussion sorry 😔

[arie-piscator]

@wiperawa I faced the same issue, and found a solution that might work for you (or others since I expect you are not working on this issue anymore). When you don't have too many distributors for each product (in this example 3), you can encode the distributor id's in the prices. For example, when you have distributors with id's 40, 63 and 70:

[
    {
        "product_id": 1,
        "name": "Computer"
        "price_1": 40000100,
        "price_2": 63000200,
        "price_3": 70000150     
    },        
   {
        "product_id": 2,
        "name": "Phone"
        "price_1": 40000500,
        "price_2": 63000700,
        "price_3": 70000750     
    },
    etc        
]

You first create price_1, price_2 etc as filterable and sortable attributes on your index. This is the limitation, of course you don't want 100 fields here.

The numbers 1, 2 and 3 have nothing to do with the distributor id's. What I did is multiply the distributor id by 1000000 (this number must be higher than your maximum price), and then add the actual price. It does't matter in which price field you store this number.

Now it is possible to filter the price for each (or a certain) distributor.

For example, when you only want to show products with a price between 50 and 150 for distributor with id 40, you can multiple the distributor id by 1000000, add the price and apply this filter:

(((price_1 >= 40000050 AND price_1 <= 40000150) OR (price_2 >= 40000050 AND price_2 <= 40000150) OR (price_3 >= 40000050 AND price_3 <= 40000150)))

It will only hit the price_1 field of the first product.