FTS inside array

Hi,

I am trying to search inside arrays when elements are objects.
Example:

{ “tshirts”: [ {“size”:“large”,“color”:“red”},{“size”:“small”,“color”:“blue”} ] }

Query:

+tshirts.size:large +tshirts.color:blue

returns false positive results finding size in the first sub-object and color in the second sub-object.
I guess it is due to the fact that tshirts.size and tshirts.color are indexed independently.

Is there any way of coupling size and color either at the indexing time or at query time?

Thanks,
Igor S

@igors,

try with double qoutes +tshirts.size:“large” +tshirts.color:“blue”

No luck, the same problem. FTS finds size and color in different tshirts array entries.

@igors,

So do you want a way to find this below ?

{ “tshirts”: [ {“size”:“large”,“color”:“blue”},{“size”:“small”,“color”:“green”} ] }

where both of them are in the same object in an array not in the same array?

N1QL
Do you need FTS? Can you use N1QL?

SELECT * FROM `bucket` WHERE
ANY i IN tshirts SATISFIES i.size = "large" AND i.color = "blue" END;

As you have observed FTS currently does not have a query type that requires child queries elements to all be satisfied by the same array elements. The index has the required information, we just don’t yet offer a way to express the query. I’ll pass this feature along for future consideration.

@househippo

Thank you for the proposed solution.
It is definitely possible to use N1QL for the presented example.
Nevertheless , sometimes FTS is the only option.
Consider, for example, theaters with array of playing movies when each movie element has a title and playing times. To find a theater by movie title (potentially partial) and playing time is definitely a task for FTS.

@mschoch

Thank you for the reply.
I think it is an essential feature to have.
Without it search within arrays of objects is very limited.
Solr does it by block joins. DataStax Enterprise by tuple query parser.
Is there any way to plug in a custom FTS indexing code?

Currently FTS allows for advanced configuration, but does not allow for any custom code execution.

I have a similar problem here, I don´t know if this limitation is solved right now in couchbase 5.5.2
Please, can anybody tell me this

Thanks

This is not yet prioritised for a particular release, And thanks for bringing this back on radar,
Certainly more such user queries helps us in prioritising this over the backlog.
Meantime, you may also share more insight into your exact use case, else we may presume that its precisely similar to above mentioned thread.

+1 here, too! Would be happy to have this feature!

Any update on this feature? It would be super useful.

You can’t use N1QL for everything because you can’t create a separate index for each query. N1QL indexes are quite dumb

We started considering this feature. But are in the process of ironing out a few nitty gritties.
-Sreekanth

1 Like

While we still need to implement the full object comparison support, I understand the advantages of flexibility that FTS provides ([https://blog.couchbase.com/search-and-rescue-7-reasons-for-n1ql-sql-developers-to-use-search/]).

Here’s somewhat useful idea for you to consider. It’s not perfect, but you can use FTS index as the first round of filter (like a bloom filter), and then apply the filters you need to apply in N1QL. The version below works in 6.5 (beta shortly), in prior versions, you’ll have to use CURL() function.

SELECT *
FROM s
INNER JOIN s s1 ON (META(s).id = META(s1).id)
UNNEST s1.tshirts AS s2
WHERE search(s, "+tshirts.color:blue +tshirts.size:large")
    AND s2.color = "blue"
    AND s2.size = "large" ;
2 Likes

This works with FTS but you have to make sure you have the right Query index set:

In your case you’d need

tshirts (dynamic)
size (child field)
color (child field)

It’ll work fine