Первый результат, повторяющийся в функциях array_agg и string_agg в postgres

Я извиняюсь, если некоторые из кода ниже является излишним, но я хочу нарисовать картину всего, что я пытаюсь сделать.

Мое намерение состоит в том, чтобы

  1. Выберите элемент из массива json pickupLocations. Есть много pickupLocationsдля каждого id_referral. См. CTE, названный raw_addressesниже., и

  2. Используйте функцию агрегации строк (например 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получить такое же поведение.

Любые мысли о том, почему было бы очень ценно.

1 ответ

  1. Можно попробовать использовать промежуточный CTE:

    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
    ), transformed_addresses AS (
        SELECT id_referral, 
        cast(pickup_address as varchar(100)) AS pickup_address
        FROM raw_addresses
    )
    SELECT id_referral,
    string_agg(pickup_address, '|') AS pickup_addresses
    FROM transformed_addresses
    GROUP BY id_referral