PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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

Tags:

2

Answers


  1. 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')
    Login or Signup to reply.
  2. 0

    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')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search