如何在PostgreSQL查询JSONB对象数组及Prisma中筛选航段数据

表结构定义 #

searches表 #

CREATE TABLE "searches" (
    "id" SERIAL NOT NULL,
    "user_id" INTEGER,
    "guest_id" INTEGER,
    "origin" TEXT NOT NULL,
    "destination" TEXT NOT NULL,
    "departure_date" TIMESTAMP(3) NOT NULL,
    "return_date" TIMESTAMP(3),
    "passengers" TEXT NOT NULL,
    "cabin_class" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "searches_pkey" PRIMARY KEY ("id")
);

flight_offers表 #

CREATE TABLE "flight_offers" (
    "id" SERIAL NOT NULL,
    "search_id" INTEGER NOT NULL,
    "flight_data" JSONB NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "flight_offers_pkey" PRIMARY KEY ("id")
);

flight_data字段为JSONB数组，示例结构：

[
    {
        "type": "flight-offer",
        "id": "3",
        "itineraries": [
            {
                "segments": [{"id": "1"}]
            },
            {
                "segments": [{"id": "2"}]
            }
        ],
        "price": {"total": "172.00"}
    },
    {
        "type": "flight-offer",
        "id": "3",
        "itineraries": [
            {
                "segments": [{"id": "1"}]
            },
            {
                "segments": [{"id": "3"}]
            }
        ],
        "price": {"total": "172.00"}
    }
]

需求 #

给定用户ID和航段ID数组（如["1", "2"]），完成以下操作：

1. 查询该用户的最新搜索记录（按created_at倒序取第一条）
2. 从关联的flight_offers表中，筛选出包含所有指定航段ID的flight-offer对象

SQL实现（PostgreSQL） #

WITH latest_search AS (
    SELECT id AS search_id
    FROM searches
    WHERE user_id = :user_id -- 替换为目标用户ID
    ORDER BY created_at DESC
    LIMIT 1
),
expanded_offers AS (
    SELECT 
        jsonb_array_elements(fo.flight_data) AS offer
    FROM flight_offers fo
    JOIN latest_search ls ON fo.search_id = ls.search_id
)
SELECT offer
FROM expanded_offers
WHERE 
    -- 检查所有指定航段ID都存在于当前offer的segments中
    (SELECT array_agg(s->>'id')
     FROM jsonb_array_elements(offer->'itineraries') AS i,
          jsonb_array_elements(i->'segments') AS s) @> ARRAY['1', '2']::text[]; -- 替换为目标航段ID数组

说明 #

• latest_search CTE获取用户最新的搜索ID
• expanded_offers CTE将flight_data数组展开为单个offer对象
• 最后通过子查询提取当前offer的所有航段ID，用@>操作符判断是否包含所有指定航段

Prisma实现 #

步骤1：定义Prisma Schema #

model Search {
  id            Int      @id @default(autoincrement())
  userId        Int?
  guestId       Int?
  origin        String
  destination   String
  departureDate DateTime
  returnDate    DateTime?
  passengers    String
  cabinClass    String
  createdAt     DateTime @default(now())
  updatedAt     DateTime
  flightOffers  FlightOffer[]
}

model FlightOffer {
  id         Int      @id @default(autoincrement())
  searchId   Int
  flightData Json
  createdAt  DateTime @default(now())
  updatedAt  DateTime
  search     Search   @relation(fields: [searchId], references: [id])
}

步骤2：查询代码 #

const userId = 1; // 替换为目标用户ID
const targetSegmentIds = ["1", "2"]; // 替换为目标航段ID数组

// 1. 获取用户最新搜索记录
const latestSearch = await prisma.search.findFirst({
  where: { userId },
  orderBy: { createdAt: 'desc' },
  include: { flightOffers: true }
});

if (!latestSearch) {
  throw new Error('该用户无搜索记录');
}

// 2. 筛选匹配所有航段的flight-offer对象
const matchedOffers = latestSearch.flightOffers.flatMap(offer => {
  return offer.flightData.filter(flightOffer => {
    // 提取当前offer的所有航段ID
    const allSegmentIds = flightOffer.itineraries.flatMap(itinerary => 
      itinerary.segments.map(segment => segment.id)
    );
    // 检查所有目标航段ID都存在
    return targetSegmentIds.every(id => allSegmentIds.includes(id));
  });
});

console.log(matchedOffers);

说明 #

• 先通过Prisma查询用户最新搜索并关联flight_offers
• 在应用层展开flightData数组，提取每个offer的航段ID，判断是否包含所有目标航段

期望结果 #

{
    "type": "flight-offer",
    "id": "3",
    "itineraries": [
        {
            "segments": [{"id": "1"}]
        },
        {
            "segments": [{"id": "2"}]
        }
    ],
    "price": {"total": "172.00"}
}

内容的提问来源于stack exchange，提问作者amo