Use Postgres functions on Supabase Client js

Question

I have table 'MyTable' containing the column 'observations' of type jsonb. The Json structure is as following:

{
  ...
  obsList: [
    {
      species: 'Goldfinch',
      number: 2
    },
    {
      species: 'House sparrow',
      number: 4
    },
    ...
  ]
}

If I want to query all the observations of species I can run the query:

select obs from  "MyTable" t, jsonb_array_elements(t.observations->'obsList') obs where obs->>'species'='Goldfinch'

How do I make the same query using the Supabase js Client library?

I tried:

this.supabase.from('MyTable')
  .select('jsonb_array_elements(observations->\'obsList\') as obs')
  .eq('obs:species', 'Goldfinch')

and

this.supabase.from('MyTable')
  .select('observations->obsList')
  .contains('jsonb_array_elements(o.observations->\'obsList\')', {species: 'Goldfinch'})

but I get the error:

column MyTable.jsonb_array_elements does not exist

For complex filtering like this, use Postgres functions. supabase.com/docs/guides/database/functions — dshukertjr
– dshukertjr, Commented Feb 19, 2024 at 8:04
Yes I figured out that was the best strategy. It's too bad we can't make raw queries. — F_sants_
– F_sants_, Commented Feb 19, 2024 at 13:49
@F_sants_ I think raw queries would be too dangerous to execute, even in a limited environment. — Laurence Isla
– Laurence Isla, Commented Feb 23, 2024 at 23:27

Laurence Isla · Accepted Answer · 2024-02-24 00:10:48Z

1

As @dshukertjr mentioned, PostgreSQL functions (and views) are the best alternative to tackle complex queries.

To give a concrete example, you could create this view:

create "MyTableObs" as
  select obs
  from "MyTable" t, jsonb_array_elements(t.observations->'obsList') obs;

And do the filtering with Supabase.js:

this.supabase.from('MyTableObs')
  .select()
  .eq('obs->>species', 'Goldfinch')

answered Feb 24, 2024 at 0:10

Laurence Isla

4582 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

F_sants_ Over a year ago

Thank you Laurence, I ended up using a function, but views also works very well.

Nitesh Baghel · Accepted Answer · 2024-02-11 16:44:46Z

0

You can query for specific array elements in JSONB column using the Superbase JS client:

const { data, error } = await superbase
 .from('MyTable)
 .select('observations:obsList(*)')
 .contains('observations.obsList[species]', 'Goldfinch')

answered Feb 11, 2024 at 16:44

Nitesh Baghel

384 bronze badges

1 Comment

F_sants_ Over a year ago

Hi NItesh, sorry but doesn't work. The second argument of contains must be an object. Moreover, shouldn't you access the json properties with ->?

Collectives™ on Stack Overflow

Use Postgres functions on Supabase Client js

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related