Выборка заказов из PG для обучения нейронной сети (тормознутый вариант, потому что генерирует много дубльданных для удобства)

array_agg_mult.sql

-- array_agg не поддерживает массивы
create aggregate array_agg_mult(anyarray) (
  SFUNC = array_cat,
  STYPE = anyarray,
  INITCOND = '{}'
);

main.sql

-- для каждого продукта, заказанного за 2 часа, выбрать корзины без самого продукта
with items as (
  select product_id, order_id from order_items where created_at > now() - '2 hour'::interval
), order_products as (
  select order_id, array_agg(product_id) as product_ids from items group by order_id
)
select product_id, array_agg_mult(array_remove(product_ids, product_id))
  from items i, order_products o
 where i.product_id = any(o.product_ids)
 group by product_id;

main_json.sql

select json_agg(json_build_object('product_id', product_id, 'variants', variants))
  from (
    -- main.sql
    with items as (
      select product_id, order_id from order_items where created_at > now() - '2 hour'::interval
    ), order_products as (
      select order_id, array_agg(product_id) as product_ids from items group by order_id
    )
    select product_id, array_agg_mult(array_remove(product_ids, product_id)) as variants
      from items i, order_products o
     where i.product_id = any(o.product_ids)
     group by product_id
   ) t;

run.sh

#!/bin/bash
cat neutron_json.sql | psql -t $DB_NAME > data.json