How to remove repetition?

SQL++
1

Hello,

I have my 1st version of query.

select
    creative_catalog.`values`.scores.karmic_hotness.score as scores_creative_karmic_hotness,
    creative_catalog.`values`.scores.sales_hotness_creative.score as scores_sales_hotness_creative,
    meta('catalog_dump').id,
    catalog_dump.`values`.title,
    (ARRAY x FOR x in scores WHEN x.score = ARRAY_MAX(scores[*].score) END)[0] as defaultSkuScore
from catalog_dump left join catalog_dump as creative_catalog on keys "prefix" || REPLACE(SPLIT(catalog_dump.id, "p")[0], "s6-", "")
    let scores = ARRAY x.scores.sales_hotness_sku FOR x in OBJECT_VALUES(catalog_dump.`values`.skus) END, timestamp = NOW_MILLIS()
where
    catalog_dump.form = 'product'
    and
    catalog_dump.`values`.product_type = 9393

Is there a way to remove repetition in the followin section? ARRAY_MAX is essentially calculating the same value. I’ve tried setting the value by using let but it wasn’t working for me.

(ARRAY x FOR x in scores WHEN x.score = ARRAY_MAX(scores[*].score) END)[0] as defaultSkuScore

I’ve tried

let scores = ARRAY x.scores.sales_hotness_sku FOR x in OBJECT_VALUES(catalog_dump.`values`.skus) END, 
timestamp = NOW_MILLIS(),
max_score = ARRAY_MAX(scores[*].score)

Thanks,
Moon

2

Chained LET is not supported.MB-19772

You can use like this.

max_score = ARRAY_MAX(OBJECT_VALUES(catalog_dump.values.skus)[*].scores.sales_hotness_sku.score)

SELECT
    creative_catalog.`values`.scores.karmic_hotness.score AS scores_creative_karmic_hotness,
    creative_catalog.`values`.scores.sales_hotness_creative.score AS scores_sales_hotness_creative,
    meta('catalog_dump').id,
    catalog_dump.`values`.title,
    (FIRST x FOR x IN OBJECT_VALUES(catalog_dump.values.skus)[*].scores.sales_hotness_sku WHEN x.score = max_score END) AS defaultSkuScore
FROM catalog_dump
LEFT JOIN catalog_dump AS creative_catalog
ON KEYS "prefix" || REPLACE(SPLIT(catalog_dump.id, "p")[0], "s6-", "")
    LET timestamp = NOW_MILLIS(),
        max_score = ARRAY_MAX(OBJECT_VALUES(catalog_dump.values.skus)[*].scores.sales_hotness_sku.score)
WHERE catalog_dump.form = 'product' AND catalog_dump.`values`.product_type = 9393;
1 Like
3

Thank you @vsr1!

That works.