Dynamic filters

Hello, I need some help with creating query to get dynamic filters and their possible values, and also create index to use those filters to query data. Here is my data model:

{
  "brand": "Apple",
  "model": "iPhone 11",
  "specifications": {
    "card_slot": false,
    "connector": "Lightning",
    "megapixels": 12,
    "memory": "4 Go"
  },
  "variants": [
    {
      "id": "1",
      "price": 809,
      "color": {
        "code": "#D6D1DC",
        "name": "purple"
      },
      "other": {
        "e_sim": "Oui",
        "storage": "64 Go"
      }
    },
    {
      "id": "2",
      "price": 859,
      "color": {
        "code": "#AB2734",
        "name": "red"
      },
      "other": {
        "screen_size": "6,1",
        "storage": "128 Go"
      }
    }
  ]
}

So what I need is to get all possible values of every fields, something like:

{
  "brand": ["Apple", "Samsung"],
  "model": ["iPhone 11", "Galaxy S7"],
  "specifications": {
    "card_slot": [true, false],
    "connector": ["Lightning", "USB C", "microUSB"],
    "megapixels": [6, 10, 12],
    "memory": ["4 Go", "16 Go", "32 Go"]
  },
  "variants": [
    {
      "price": [809, 859],
      "color": [
        {
          "code": "#D6D1DC",
          "name": "purple"
        },
        {
          "code": "#AB2734",
          "name": "red"
        }
      ],
      "other": {
        "storage": ["64 Go", "128 Go", "256 Go"],
        "screen_size": ["6,1", "5,2"]
      }
    }
  ]
}

Then I want to create a covering index for this kind of query, or something like that that can achieve what I want:

SELECT * FROM products
WHERE brand = "Apple"
  AND specifications.card_slot = true
  AND variants.color.name = "purple"
  AND variants.price < 800

Thanks for your help

1 Like
SELECT brand,
       model,
       {card_slot, connector, megapixels, memory } AS specifications,
       {price, color, "other" : {storage, screen_size} } AS variants
FROM products AS d
UNNEST d.variants AS v
LETTING brand = ARRAY_AGG(DISTINCT d.brand),
        model = ARRAY_AGG(DISTINCT d.model),
        card_slot = ARRAY_AGG(DISTINCT d.specifications.card_slot),
        connector = ARRAY_AGG(DISTINCT d.specifications.connector),
        megapixels = ARRAY_AGG(DISTINCT d.specifications.megapixels),
        memory = ARRAY_AGG(DISTINCT d.specifications.memory),
        price = ARRAY_AGG(DISTINCT v.price),
        color = ARRAY_AGG(DISTINCT v.color),
        storage = ARRAY_AGG(DISTINCT v.other.storage),
        screen_size = ARRAY_AGG(DISTINCT v.other.screen_size);

When use * covering is not possible. The right query is as follows

SELECT * FROM products
WHERE brand = "Apple"
  AND specifications.card_slot = true
  AND ANY v IN variants SATISFIES v.color.name = "purple" AND AND v.price < 800 END;

CREATE INDEX ix1 ON products(brand, specifications.card_slot, DISTINCT ARRAY v.color.name FOR v IN variants END);

Checkout

https://index-advisor.couchbase.com/indexadvisor/#1

Thanks for you answer, sorry but maybe I wasn’t clear. The fields variants.other and specifications are completely dynamics, there are not the same between documents, and therefore, I canno’t create index for specific fields like you did!

You need to have at least one constant field in index and query. Otherwise use primary index.

example:

CREATE INDEX ix1 ON products(brand);

After a lot of work, I found how to do it. For example: for specifications:

Index

CREATE INDEX specifications ON `bucket`(ALL OBJECT_INNERPAIRS(specifications, brand))

Request to get filters

SELECT specs.name, ARRAY_AGG(DISTINCT specs.val) as `values`
FROM `bucket`
UNNEST OBJECT_INNER_PAIRS(specifications) as specs
WHERE brand = "Apple"
GROUP BY specs.name

Request to get documents

SELECT count(*)
FROM reMarket
WHERE brand = "Apple"
  AND ANY specs IN object_innerpairs(specifications)
          SATISFIES specs = { "name": "megapixels", "val": 12}
      END

If anyone interested about the other requests don’t hesitate to ping me