Hello everyone, I have a table called

sources

which has a column

sourceAddresses

of type

jsonb[]

of the following structure:

[
  {
    "address": "0xsomething1",
    "type": "SOME_PROPERTY_1"
  },
  {
    "address": "0xsomething2",
    "type": "SOME_PROPERTY_2"
  }
]

I want to check for a row in which the array in the column sourcesAddresses contains a certain source, identified by the

address

property. I tried to implement the code:

const { data, error } = await supabase
        .from("sources")
        .select("*")
        .contains("address:sourceContractAddresses->>address", contractAddress);

Unfortunately it returns

null

Can someone please guide me?

It seems to me that you are you are either not using the correct operator or the correct field (depending on the point of view): - the contains() method from the Supabase UI adds the ?cs PostgREST parameter in the request; - as seen in the PostgREST doc, the cs parameter corresponds to the @> PostgreSQL operator (https://postgrest.org/en/v9.0/api.html#operators) - the @> operator takes 2 arrays and tells you whether the elements in the second array ALL appear as elements in the first array (if you want to check for a single elements, you still need to pass an array with a single element as the second array): https://www.postgresql.org/docs/14/functions-array.html

@User could you suggest how i should modify my query?

what is contained in your contractAddress variable?

@User thank you for jumping in to help, really appreciate it! Just FYI, the following does not work either:

const { data, error } = await supabase
        .from("sources")
        .select("*")
        .contains("address:sourceContractAddresses->>address", contractAddress);

It is the string corresponding to the

address

I want to search

this is the same as the one posted above, did you mean to paste something else?

0xsomething1

as per my example

Sorry, my bad

const { data, error } = await supabase
        .from("sources")
        .select("*")
        .contains("sourceContractAddresses", [`address: ${contractAddress}`]);

Ok, I am pretty sure the first query has no way of working because (a) the first parameter passed to the contains() function is actually NOT an array and (b) because the second parameter you give it does NOT contain a json element which can match the json elements in the array column (the json elements in the array column have 'addres' and 'type' fields while you are passing a single string value)

This is very helpful, thanks @chipilov . @Gita Alekhya Paul | Quinence please try the exact shape in the second query and see if that works.

Maybe try something like this: contains("sourceContractAddresses", [{ address:

${contractAddress}

, type:

${SOME_PROPERTY_2}

}] - this, of course, assumes that you also have type

I actually do not know the type

if you do not know the type and only want to search by address, my guess is that you need a different operator (i.e. you cannot use contains())

i actually cannot figure out the exact query I need for this

let me try a couple of things and get back to you in 10min

But could'nt figure out what to use

btw, do you need an array of jsonb object (i.e. jsonb[])? can you not just make it a jsonb and define the array on the json-level (i.e. have the array be a JSON array and NOT a PostgreSQL array)?

Hi @User , thanks for your immense help So, I re-structured the

sourceContractAddresses

column into a JSON of key

address

and value

type

So, a sample column

sourceContractAddresses

of a row would be:

{
  "0xsomething1": "SOME_PROPERTY_1",
  "0xsomething2": "SOME_PROPERTY_2"
}

Then I run the following query:

const { data, error } = await supabase
        .from("sources")
        .select(`id, sourceContractAddresses->>${contractAddress}`)
        .neq(
            `sourceContractAddresses->>${contractAddress}`,
            `{ ${contractAddress}: null }`
        );

no worries. ultimately, it's your call but I am not sure if using values as keys is a good idea

true, but in our use-case it is okay to use the values as keys

ok, your call