Sorting by a field selected in an array

General Discussions - English
1

Hi manticore community,

I’ve got troubles finding a way to do what i need using manticore, i’m sorry this is a bit complex and i really hope someone will be able to help me :pray:t3:

I have a products index that looks like this :

'product_id' => ['type' => 'bigint'],
'options_combination' => ['type' => 'json'],
'sku' => ['type' => 'string'],
'weight' => ['type' => 'float'],
'created_at' => ['type' => 'timestamp'],
'updated_at' => ['type' => 'timestamp'],
'images' => ['type' => 'json'],
'categories' => ['type' => 'json'],
'prices' => ['type' => 'json'],

And the dataset looks like this as an example :

'product_id' => 1,
'options' => [
    {"option_id":1,"option_value_id":1},
    {"option_id":2,"option_value_id":6}
], 
'sku' => "453-F6H-2",
'weight' => 1.970000,
'created_at' => 1679945503,
'updated_at' => 1679945503,
'images' => [
    {
        "id":19,"imageable_id":1,"imageable_type":"App\\Models\\Product","absolute_path":"https://minio.laravel.docker/products/1/2/6/2274.webp","created_at":"1679945503","updated_at":"1679945503"
    },
    {
        "id":20,"imageable_id":1,"imageable_type":"App\\Models\\Product","absolute_path":"https://minio.laravel.docker/products/1/2/6/22744.jpeg","created_at":"1679945503","updated_at":"1679945503"
    }
],
'categories' => [
    {
        "id":1,"name":"Clothing","created_at":"1679945503","updated_at":"1679945503",
    },
    {
        "id":2,"name":"T-shirt","created_at":"1679945503","updated_at":"1679945503",
    }
],
'prices' => [
    {
        "id":1,"currency_id":1,"country_id":1,"product_id":1,"without_taxes":22.700000,"created_at":"1679945503","updated_at":"1679945503"
    },
    {
        "id":2,"currency_id":2,"country_id":1,"product_id":1,"without_taxes":89.700000,"created_at":"1679945503","updated_at":"1679945503"
    },
    {
        "id":3,"currency_id":3,"country_id":1,"product_id":1,"without_taxes":99.600000,"created_at":"1679945503","updated_at":"1679945503"
    },
    {
        "id":4,"currency_id":1,"country_id":2,"product_id":1,"without_taxes":83.900000,"created_at":"1679945503","updated_at":"1679945503"
    },
    {
        "id":5,"currency_id":2,"country_id":2,"product_id":1,"without_taxes":63.500000,"created_at":"1679945503","updated_at":"1679945503"
    },
    {
        "id":6,"currency_id":3,"country_id":2,"product_id":1,"without_taxes":100,"created_at":"1679945503","updated_at":"1679945503"
    },
    {
        "id":7,"currency_id":1,"country_id":3,"product_id":1,"without_taxes":49.900000,"created_at":"1679945503","updated_at":"1679945503"
    }
]

What i want to do is sort the products according to the without_taxes field, if currency_id = 1 AND country_id = 1.

I hope this will be explained enough for someone to understand my problem :thinking:
Thanks !

2

Are you looking for smth like this:

mysql> drop table if exists t; create table t(without_taxes int, current_id int, country_id int); insert into t(without_taxes, current_id, country_id) values(1, 1, 1),(1, 0, 0),(1, 0, 1),(1, 1, 0),(2, 1, 1),(2, 1, 0); select *, if(current_id=1 and country_id=1, without_taxes, 0) s from t order by s desc;
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
create table t(without_taxes int, current_id int, country_id int)
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t(without_taxes, current_id, country_id) values(1, 1, 1),(1, 0, 0),(1, 0, 1),(1, 1, 0),(2, 1, 1),(2, 1, 0)
--------------

Query OK, 6 rows affected (0.00 sec)

--------------
select *, if(current_id=1 and country_id=1, without_taxes, 0) s from t order by s desc
--------------

+---------------------+---------------+------------+------------+------+
| id                  | without_taxes | current_id | country_id | s    |
+---------------------+---------------+------------+------------+------+
| 1515277419643142173 |             2 |          1 |          1 |    2 |
| 1515277419643142169 |             1 |          1 |          1 |    1 |
| 1515277419643142170 |             1 |          0 |          0 |    0 |
| 1515277419643142171 |             1 |          0 |          1 |    0 |
| 1515277419643142172 |             1 |          1 |          0 |    0 |
| 1515277419643142174 |             2 |          1 |          0 |    0 |
+---------------------+---------------+------------+------------+------+
6 rows in set (0.00 sec)

?

3

Alas, suspect will have to ‘denormalize’ your prices as individual documents to be able to sort by that, like Sergey demonstrates. Each price is its own “document” in the index. Would have duplicate all the attributes (and fields) into each document.

While ANY() or ALL() can make ‘tests’ against data inside JSON arrays, you can’t extract (AFAIK!) a value, to make something can sort on. There isn’t a JSON_EXTRACT() like see in mysql for example.

4

Hi Sergey and barryhunter, thanks for your answers.

I will try the IF() syntax suggested by Sergey and tell you guys it it works, maybe i’ll have to use null instead of 0 to avoid putting items with 0 in the sorting result.

I think that i may have to index each prices in it’s own document like you said, but i was hoping for a solution to avoid that, maybe i need to re-think my dataset organization it feels like something is wrong…

Thank you guys i’ll tell you if your solution using IF() brings me to something working …

Do you guys recommend json type for index fields holding arrays of data or did i miss another data type ?

5

I just read about mysql methods you’re referring here and i think you are right, so this means i need to think of a way to rework my index. I will index products and prices separately. Thanks for your help !

Edit : Just to add some informations this is an issue from typesense that is very similar to mine : Sorting by a nested field in array · Issue #867 · typesense/typesense · GitHub