Django query (select, count where)

I'm trying to recreate the following sql query in the django ORM

CASE WHEN
        (SELECT COUNT(id) FROM offer WHERE offer.product_id = p.id) < 1
    THEN
        '[]'::JSONB
    ELSE
    (
        SELECT
        JSON_AGG(JSON_BUILD_OBJECT('name', o_sub.name, 'status', o_sub.status))
        FROM offer o_sub
        WHERE o_sub.product_id = p.id
        LIMIT 2 -- TWO IN PREVIEW LIST.
    )::JSONB
    END AS _offers

But I haven't been hable to do it so far I had something like this:

merchant_offers=Case(
                    When(
                        number_of_offers__lt=1 , then=JSONObject(
                            offer_name="offers__name",
                            offer_value="offers__merchant_group_fees__value"
                        )
                    ),
                )

But i dont really know how to do the second query the one with the JSON_AGG

Back to Top