postgresql - Postgress Group BY 与 to_tsquery

Question

我使用 psql (11.7)。我有表 ptoducts 我在其中创建了 GIN 索引以按文本字段搜索

create index ndsprc_swedish_custom_index on products
using GIN(to_tsvector('pg_catalog.swedish',coalesce(name,'')||' '||coalesce(description,'')||' '||coalesce(sku,'')||' '||coalesce(price,0)||' '||coalesce(category,'')||' '||coalesce(brand,'')||' '||coalesce(shop,'')))

并且有我对具有限制和偏移的搜索产品的查询

SELECT                         
products_alias.id,
products_alias.sku,
products_alias.name AS "name",
products_alias.description,
products_alias.category,
products_alias.price,
products_alias.shipping,
products_alias.currency,
products_alias.instock,
products_alias.product_url AS "productUrl",
products_alias.image_url AS "imageUrl",
products_alias.tracking_url AS "trackingUrl",
products_alias.brand,
products_alias.shop,
products_alias.original_price AS "originalPrice",
products_alias.ean,
products_alias.manufacturer_article_number AS "manufacturerArticleNumber",
products_alias.extras,
products_alias.created_at AS "createdAt",
products_alias.brand_relation_id AS "brandRelationId",
products_alias.shop_relation_id AS "shopRelationId",
array_agg(DISTINCT cpt.category_id) AS categoryIds,
COUNT(DISTINCT uip.id) as "numberOfEntries",   
ts_rank_cd(to_tsvector('pg_catalog.swedish',coalesce(name,'')||' '||coalesce(description,'')||' '||coalesce(sku,'')||' '||coalesce(price,0)||' '||coalesce(category,'')||' '||coalesce(brand,'')||' '||coalesce(shop,'')), query_search) AS rank

FROM products products_alias 

JOIN to_tsquery('pg_catalog.swedish', 'dress:*') query_search
ON to_tsvector('pg_catalog.swedish',coalesce(name,'')||' '||coalesce(description,'')||' '||coalesce(sku,'')||' '||coalesce(price,0)||' '||coalesce(category,'')||' '||coalesce(brand,'')||' '||coalesce(shop,'')) @@ query_search

LEFT JOIN product_category cp on cp.product_id = products_alias.id
LEFT JOIN product_category cpt on cpt.product_id = products_alias.id
LEFT JOIN user_ip_product uip on uip.products_id = products_alias.id               

GROUP BY products_alias.id, query_search.query_search ORDER BY rank DESC, "numberOfEntries" DESC

LIMIT 10
OFFSET 0

有些产品有相同product_url但不同extras（JSONB类型）。Hot to group by GROUP BY product_url（VARCHAR类型）并将extras结果合并到一个字段中并保存LIMIT和OFFSET？

没有搜索它看起来像这样

SELECT                         
products_alias.product_url,
array_agg(products_alias.id) AS ids,
array_agg(products_alias.extras) AS extras_merge

FROM products products_alias 
GROUP BY products_alias.product_url

我有正确的结果，因为我有两个相同的 idproduct_url

product_url | ids         | extras_merge
7942010-80   | {3004,3002} | {{extras1},{extras2}}
7942010-81   | {3003}      | {{extras1}}

当我使用子查询时，它可以工作，但是如何将其重构为一个查询？

SELECT                         

array_agg(DISTINCT main_products_alias.id) AS ids,
array_agg(DISTINCT main_products_alias.name) AS names,
array_agg(DISTINCT main_products_alias.extras) AS extras

FROM (
SELECT                         
products_alias.id,
products_alias.sku,
products_alias.name AS "name",
products_alias.description,
products_alias.category,
products_alias.price,
products_alias.shipping,
products_alias.currency,
products_alias.instock,
products_alias.product_url AS "productUrl",
products_alias.image_url AS "imageUrl",
products_alias.tracking_url AS "trackingUrl",
products_alias.brand,
products_alias.shop,
products_alias.original_price AS "originalPrice",
products_alias.ean,
products_alias.manufacturer_article_number AS "manufacturerArticleNumber",
products_alias.extras,
products_alias.created_at AS "createdAt",
products_alias.brand_relation_id AS "brandRelationId",
products_alias.shop_relation_id AS "shopRelationId",
array_agg(DISTINCT cpt.category_id) AS categoryIds,
COUNT(DISTINCT uip.id) as "numberOfEntries"

,ts_rank_cd(to_tsvector('pg_catalog.swedish',coalesce(name,'')||' '||coalesce(description,'')||' '||coalesce(sku,'')||' '||coalesce(price,0)||' '||coalesce(category,'')||' '||coalesce(brand,'')||' '||coalesce(shop,'')), query_search) AS rank

FROM products products_alias 

JOIN to_tsquery('pg_catalog.swedish', 'dress:*') query_search
ON to_tsvector('pg_catalog.swedish',coalesce(name,'')||' '||coalesce(description,'')||' '||coalesce(sku,'')||' '||coalesce(price,0)||' '||coalesce(category,'')||' '||coalesce(brand,'')||' '||coalesce(shop,'')) @@ query_search

LEFT JOIN product_category cp on cp.product_id = products_alias.id
LEFT JOIN product_category cpt on cpt.product_id = products_alias.id
LEFT JOIN user_ip_product uip on uip.products_id = products_alias.id               

GROUP BY products_alias.id, query_search.query_search ORDER BY rank DESC, "numberOfEntries" DESC
) AS main_products_alias

GROUP BY main_products_alias."productUrl"

标签： postgresql