Filter efficiently using jsonb with sdk

Hello,
I'm trying to make a query to a table that includes a bunch of data in a jsonb field. I need to filter this data from a specific field inside the json that is indexed in postgres.
The raw SQL query that is efficient is the following:

select
      "public"."reports".*
    from
      "public"."reports"
    where
      "public"."reports"."json" -> 'valuations' -> 'providers' -> 0::int ->> 'id' = '{id}'
    limit
      1

select
      "public"."reports".*
    from
      "public"."reports"
    where
      "public"."reports"."json" -> 'valuations' -> 'providers' -> 0::int ->> 'id' = '{id}'
    limit
      1

Using the python sdk that makes the following code:

response = (
            supabase.from_("reports")
            .select("*")
            .filter("json->valuations->providers->0->>id", "eq", id)
            .limit(1)
            .execute()
        )

response = (
            supabase.from_("reports")
            .select("*")
            .filter("json->valuations->providers->0->>id", "eq", id)
            .limit(1)
            .execute()
        )

However the issue is that using the sdk the request is taking a very long time. After analyzing in Query Performance page. And I found out that the actuall SQL query run from the sdk is the following:

select
      "public"."reports".*
    from
      "public"."reports"
    where
      to_jsonb("public"."reports"."json") -> $1 -> $2 -> $3::int ->> $4 = $5
    limit 1

select
      "public"."reports".*
    from
      "public"."reports"
    where
      to_jsonb("public"."reports"."json") -> $1 -> $2 -> $3::int ->> $4 = $5
    limit 1

The issue is the use of "to_jsonb" that prevents the query to use the indexes on the jsonb field. That is very UNefficient.

Is there any way to avoid this using the sdk ?

Filter efficiently using jsonb with sdk

select
      "public"."reports".*
    from
      "public"."reports"
    where
      "public"."reports"."json" -> 'valuations' -> 'providers' -> 0::int ->> 'id' = '{id}'
    limit
      1

select
      "public"."reports".*
    from
      "public"."reports"
    where
      "public"."reports"."json" -> 'valuations' -> 'providers' -> 0::int ->> 'id' = '{id}'
    limit
      1

Using the python sdk that makes the following code:

response = (
            supabase.from_("reports")
            .select("*")
            .filter("json->valuations->providers->0->>id", "eq", id)
            .limit(1)
            .execute()
        )

response = (
            supabase.from_("reports")
            .select("*")
            .filter("json->valuations->providers->0->>id", "eq", id)
            .limit(1)
            .execute()
        )

select
      "public"."reports".*
    from
      "public"."reports"
    where
      to_jsonb("public"."reports"."json") -> $1 -> $2 -> $3::int ->> $4 = $5
    limit 1

select
      "public"."reports".*
    from
      "public"."reports"
    where
      to_jsonb("public"."reports"."json") -> $1 -> $2 -> $3::int ->> $4 = $5
    limit 1

The issue is the use of "to_jsonb" that prevents the query to use the indexes on the jsonb field. That is very UNefficient.

Is there any way to avoid this using the sdk ?

Filter efficiently using jsonb with sdk

Similar Threads

Filter efficiently using jsonb with sdk

Similar Threads

Similar Threads

Similar Threads