How to query JSONB array of objects in PostgreSQL

Question

How to filter flight_data in "flight_offers" table based on provided segment IDs in Prisma.io?

I have two tables: "searches" and "flight_offers". The db structure for "searches" is as follows:

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")
);

The db structure for "flight_offers" is as follows:

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")
);

This is an example of what the flight_data column in "flight_offers" table contains:

[
    {
        "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"
        }
    }
]

Given a user ID and an array of segment IDs ["1", "2"], I want to query the last search a user did in the "searches" table and then filter the flight_data column in the "flight_offers" table to only return the object that has segments with matching IDs. I am currently using Prisma.io to manage my database, but I am open to using both Prisma and SQL queries to solve this.

desire result is:

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

Thanks

How to query JSONB array of objects in PostgreSQL

0 Answers0