Using IF function in FACET

General Discussions - English
1

Hello all,

wanted to do FACET calculation on json fields, but with fallback if field don’t exist.

This query counts only zeros, but you get the idea that if field p_1_17 is missing then use field p_1

Can we formulate this query other ways?

SELECT id FROM products WHERE `id` > 0
FACET (
  IF(
    ANY(x = 'p_1_17' FOR x IN attribs.attrib_1.permutations.keys),
    attribs.attrib_1.permutations.p_1_17,
    attribs.attrib_1.permutations.p_1
  )
) AS attrib_5_permutation;
2

Hi all, since it is not possible (i think) use conditions for FACET, i’m thinking of using udf for query, where i can apply all conditions and return computed values.
But i have encountered another obstacle.
From docs i read that i cannot return MVA or json https://manual.manticoresearch.com/UDFs_and_Plugins/UDF#Return-types-of-UDF
So FACET will not count values like [2,4,7], that i want to return.
Or is the documentation old and i can return MVA for using in FACET function?

For your idea how the json fields looks like

"variants": [
        {
            "attrib_1": 1,
            "attrib_5": 23,
            "availability": 6
        },
        {
            "attrib_1": 17,
            "attrib_5": 21,
            "availability": 4
        },
        {
            "attrib_1": 17,
            "attrib_5": 22,
            "availability": 2
        },
        {
            "attrib_5": 24,
            "availability": 8
        },
        {
            "attrib_1": 1,
            "attrib_5": 22,
            "availability": 6
        }
    ]

The udf function should return all values for attrib_5, but only for those variants where attrib_1 is 1 or 17 for example.

So my only option is to use some plugin or udf.

3

So, by running UDF function, do you think FACET will work with returned MVA?
Don’t know the reason why it is restricted only for int, float and string.
But if it was possible to work with MVA and JSON this would be a powerfull additon to manticore.
I’m running out of options.
Starting thinking of rework documents in my index, so each variant will be one index record.
This rework will be hard to implement, months of changes and testing.