从数组中删除不需要的元素 [英] Remove unwanted elements from arrays

问题描述

我有一个表 scraped_listings 和一个表 scraped_categories 。列 scraped_listings.categories 是一个整数[]数组，其中包含 scraped_categories 中的行ID。

以某种方式（可能是由于我不记得犯的一个错误），一些scraped_listings行的ID属于不属于类别行的类别（我怀疑那些行已被删除）。 / p>

我有以下查询，这给了我受影响的行：

  SELECT * FROM scraped_listings一个
 JOIN（
 SELECT array_agg（id）AS ids 
 FROM scraped_categories 
）b不在a.categories上< @ b.ids; 
  

我现在要做的是从<$ c中删除无效的ID $ c>类别用于此查询找到的行-如果数组中的项目不是有效的scraped_category ID，则应将其删除。

我该怎么做？

解决方案

Postgres 9.2或更早版本：

  UPDATE scraped_listings s 
 SET类别= up.categories 
 FROM（
选择a.pkey，array_agg（a .id）AS类别
 FROM（
 SELECT pkey，嵌套（类别）AS id 
 FROM scraped_listings 
）a 
 JOIN scraped_categories s的使用（id）-消除不需要的ID 
）up 
 WHERE s.pkey = up.pkey 
  

pkey 是 scraped_listings 的未公开主键列。

在Postgres 9.3 中，您将对相关的<$ c $使用 LATERAL c> unnest（）：

  UPDATE scraped_listings s 
 SET类别= up.categories 
 FROM（
选择a.pkey，array_agg（a.id）AS类型
 FROM（
选择pkey，c_id AS ID 
 FROM scraped_listings l，nest（l .categories）c_id-隐含的横向
）a 
 JOIN scraped_categories s的使用（id）-消除不需要的ID 
）up 
 WHERE s.pkey = up.pkey 
  

或者您安装其他模块 intarray ，它为 int [] ，例如：

  int []-int int []删除匹配权限的条目数组
  

中的参数

I have a table scraped_listings and a table scraped_categories. The column scraped_listings.categories is an integer[] array containing ids of rows in scraped_categories.

Somehow (probably through a mistake I don't remember making), some scraped_listings rows have ids in categories that do NOT belong to a category row (those rows have been deleted, I suspect).

I have the following query, which gives me the rows affected:

SELECT * FROM scraped_listings a
JOIN (
  SELECT array_agg(id) AS ids
  FROM scraped_categories
  ) b ON NOT a.categories <@ b.ids;

What I'd like to do now is to remove the ids which are not valid from categories for the rows found by this query - if an item in the array is not a valid scraped_category id, it should be dropped.

How might I do this?

解决方案

Postgres 9.2 or earlier:

UPDATE scraped_listings s
SET   categories = up.categories
FROM (
    SELECT a.pkey, array_agg(a.id) AS categories
    FROM  (
        SELECT pkey, unnest (categories) AS id
        FROM   scraped_listings
        ) a
    JOIN scraped_categories s USING (id) -- eliminates unwanted ids
    ) up
WHERE s.pkey = up.pkey

pkeybeing the undisclosed primary key column of scraped_listings.

In Postgres 9.3 you would use LATERAL for the correlated unnest():

UPDATE scraped_listings s
SET   categories = up.categories
FROM (
    SELECT a.pkey, array_agg(a.id) AS categories
    FROM  (
        SELECT pkey, c_id AS id
        FROM   scraped_listings l, unnest(l.categories) c_id  -- implicit LATERAL
        ) a
    JOIN scraped_categories s USING (id) -- eliminates unwanted ids
    ) up
WHERE s.pkey = up.pkey

Or you install the additional module intarray, that ships additional operators for int[], like:

int[] - int     int[]   remove entries matching right argument from array

这篇关于从数组中删除不需要的元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！