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