从一个json数组中选择一个元素，这个数组名为picupLocations。每个id_referral都有很多个picupLocations。请参阅下面名为raw_addresses的CTE。，和

使用字符串聚合函数（例如string_agg）为每个id_referral创建一个分隔的地址字段。

换句话说，我希望能够从

#id_referral    pickup_addresses
#1  4265 Hillsdale Ave. NE, Grand Rapids, MI 49525
#1  3060 Cheney Ave. NE, Grand Rapids, MI 49525
#2  805 Kendalwood St. NE, Grand Rapids, MI 49505
#2  711 Edgewood St. NE, Grand Rapids, MI 49505

在我的raw_addressesCTE

到

#id_referral    pickup_addresses
#1  4265 Hillsdale Ave. NE, Grand Rapids, MI 49525 | 3060 Cheney Ave. NE, Grand Rapids, MI 49525
#2  805 Kendalwood St. NE, Grand Rapids, MI 49505 | 711 Edgewood St. NE, Grand Rapids, MI 49505

在我最后的选择中。

然而，我现在得到了以下结果

#id_referral    pickup_addresses
#1  4265 Hillsdale Ave. NE, Grand Rapids, MI 49525 | 4265 Hillsdale Ave. NE, Grand Rapids, MI 49525
#2  805 Kendalwood St. NE, Grand Rapids, MI 49505 | 805 Kendalwood St. NE, Grand Rapids, MI 49505

使用下面的代码时。

 WITH raw_addresses AS (
         SELECT sr.id AS id_referral,
        --parsing a json array which has many pickupLocations for a single id_referral
            json_array_elements(sr."pickupLocations") -> 'pickupLocationAddress' AS pickup_address
           FROM "ServiceReferrals" sr
        )
--want to roll the pickup addresses into a single pipe-delimited field (willing work through an array too as shown with array_agg, but same problem there)
 SELECT raw_addresses.id_referral,
    string_agg(cast(pickup_address as varchar(100)), '|') AS pickup_addresses
   FROM raw_addresses
  GROUP BY raw_addresses.id_referral

出于某种原因，string_agg函数正在为每个id_referral重复第一个值。我也尝试过agg_array并获得相同的行为。

任何关于为什么的想法都将不胜感激。