Server-side SQL query. Got the JSON but how do I display?

Need help
1

Hi- I’m using Supabase as my backend.

I have a bit of issue on how to display the data from my SQL query. Currently, the process is supabase database > sql call from bubble backend > display onto bubble frontend

However, I’m unsure how to go from bubble backend to frontend in this case.

2

Hi @steven.h.liu.1 ,

You need to initialize the Bubble API call first before you can use it anywhere in your app (like repeating groups, text fields, etc.).

Example:

  1. Go to API Connector

  2. Set up your Supabase endpoint (URL + headers)

  3. Click “Initialize call”

  4. Bubble will fetch sample data and create the fields (like id, name, email)

Only after this step, you’ll be able to:

  • Bind data to UI elements

  • Use it in workflows

  • Access returned fields dynamically

If you skip initialization, Bubble won’t know what data structure to expect, so nothing will show up.

someother edge cases where it might not work are:

  • No data returned during initialization

  • RLS blocking the query

  • Incorrect API setup

Let me know if you get stuck

3

Hi @mani2726,

Thanks!

On top of the normal API pull (I set up the API connector for that already), I’m also doing some SQL query from Bubble backend. How do I connect this to the API connector?

image
image596×311 7.31 KB

4

May i know the response of this call from supabase?
your query is heavy?

5

currently just testing, so it’s just a single value, but the json payload will likely be something like this below. I also will have jsonb as columns (“audience_demographics” & “gender”) as well.

{
  "account_id": 1,
  "start_date": 20240401,
  "duration": 30,
  "metric_type": "engagement",
  "platform_id": 1,
  "audience_demographics": {
    "age": {
      "18-24": 28,
      "25-34": 311,
      "35-44": 105,
      "45-54": 12,
      "55-64": 8,
      "65+": 3
    },
    "gender": {
      "F": 388,
      "M": 101,
      "Other": 73
    }
  }
}
6

Hi @steven.h.liu.1 ,

you can use toolbox to read the nested jsonb values. or you can alter your sql query like below,
(audience_demographics->‘age’->>‘18-24’)::int as age_18_24,
(audience_demographics->‘gender’->>‘F’)::int as female_count etc

7

Hey Mani- apology. Do you mind elaborating more? I’m quite new to this. Specifically, my questions are:

  1. How do I go from the SQL json payload from the bubble backend to connect it to API connector?
  2. Could you explain more on the toolbox to read jsonb?

Thanks!