CODEWITHSUNDEEP
postgresqlsupabasepostgrestsupabase-js
F_sants_
F_sants_

Reputation: 54

Use Postgres functions on Supabase Client js

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

Upvotes: 0

Views: 487

Answers (2)

Laurence Isla
Laurence Isla

Reputation: 458

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')

Upvotes: 1

Nitesh Baghel
Nitesh Baghel

Reputation: 38

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')

Upvotes: 0

Related Questions