Is Multi Valued index supported in singlestore for json arrays?

I have a JSON column with array. In fact, in this column I have a list of some IDs and in my queries I refer to this column as Json_array_contains_double(“columnName”, 1234)
Is there a way to index this ?

Welcome to the forums v.dhakate! :wave:

Are you running the managed or self-hosted service? What version number?

Hi @v.dhakate,

Can you tell me more about your app and why you want to index the array?

You can’t index the json array right now. We are working on a feature that will let the array be stored in columnstore format for fast filtering. That might help.

If you really need it indexed, you may want to consider normalizing the numbers into another table and putting an index on the other table, and (maybe) indexes on the join columns of the tables.

Also, check out our TABLE function. You can do things like this:

singlestore> create table t(a int, j json);
Query OK, 0 rows affected (0.03 sec)

singlestore> insert t values (1,'[500,600,700]');
Query OK, 1 row affected (0.23 sec)

singlestore> insert t values (2,'[501,601,700]');
Query OK, 1 row affected (0.00 sec)

singlestore> select * from t where json_array_contains_double(j,501);
+------+---------------+
| a    | j             |
+------+---------------+
|    2 | [501,601,700] |
+------+---------------+
1 row in set (0.04 sec)

singlestore> select a, tbl.* from t, table(json_to_array(j)) tbl;
+------+-----------+
| a    | table_col |
+------+-----------+
|    1 | 500       |
|    1 | 600       |
|    1 | 700       |
|    2 | 501       |
|    2 | 601       |
|    2 | 700       |
+------+-----------+
6 rows in set (0.03 sec)

Also, depending on your app, you might be able to store the json array as text and use full text search on it.

1 Like

Thanks . I will try using full text search.

1 Like