https://supabase.com/ logo
#javascript
Title
# javascript
g

Gita Alekhya Paul | Quinence

12/06/2021, 6:35 AM
Hello everyone, I have a table called
sources
which has a column
sourceAddresses
of type
jsonb[]
of the following structure:
Copy code
[
  {
    "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:
Copy code
const { data, error } = await supabase
        .from("sources")
        .select("*")
        .contains("address:sourceContractAddresses->>address", contractAddress);
Unfortunately it returns
null
Can someone please guide me?
c

chipilov

12/06/2021, 8:10 AM
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
g

Gita Alekhya Paul | Quinence

12/06/2021, 8:10 AM
@User could you suggest how i should modify my query?
c

chipilov

12/06/2021, 8:11 AM
what is contained in your contractAddress variable?
r

Ratul Saha | Quinence

12/06/2021, 8:12 AM
@User thank you for jumping in to help, really appreciate it! Just FYI, the following does not work either:
Copy code
const { data, error } = await supabase
        .from("sources")
        .select("*")
        .contains("address:sourceContractAddresses->>address", contractAddress);
g

Gita Alekhya Paul | Quinence

12/06/2021, 8:12 AM
It is the string corresponding to the
address
I want to search
c

chipilov

12/06/2021, 8:13 AM
this is the same as the one posted above, did you mean to paste something else?
Can you provide an example value?
g

Gita Alekhya Paul | Quinence

12/06/2021, 8:13 AM
0xsomething1
as per my example
r

Ratul Saha | Quinence

12/06/2021, 8:14 AM
Sorry, my bad
Copy code
const { data, error } = await supabase
        .from("sources")
        .select("*")
        .contains("sourceContractAddresses", [`address: ${contractAddress}`]);
c

chipilov

12/06/2021, 8:17 AM
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)
The second query that @User sent probably doesn't work because the value in the second parameter again does NOT match the shape of the json elements in the array. That is, if you have an array [{ 'a': 1, 'b': '2' }, { 'a': 10, 'b': 20 }], you are trying to match that against a string looking like 'a: 10' (IMPORTANT, this is a string, NOT a json object)
r

Ratul Saha | Quinence

12/06/2021, 8:19 AM
This is very helpful, thanks @chipilov . @Gita Alekhya Paul | Quinence please try the exact shape in the second query and see if that works.
c

chipilov

12/06/2021, 8:23 AM
Maybe try something like this: contains("sourceContractAddresses", [{ address:
${contractAddress}
, type:
${SOME_PROPERTY_2}
}] - this, of course, assumes that you also have type
g

Gita Alekhya Paul | Quinence

12/06/2021, 8:24 AM
I actually do not know the type
c

chipilov

12/06/2021, 8:25 AM
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())
as far as I can tell the '@>' operator is also defined for JSONB types, but I think the problem here is that have mixed an array type with a JSONB type and so the '@>' will be applied against the array type
g

Gita Alekhya Paul | Quinence

12/06/2021, 8:28 AM
i actually cannot figure out the exact query I need for this
I too was sceptical about the operator
c

chipilov

12/06/2021, 8:28 AM
let me try a couple of things and get back to you in 10min
g

Gita Alekhya Paul | Quinence

12/06/2021, 8:28 AM
But could'nt figure out what to use
Thanks a lot 👍
c

chipilov

12/06/2021, 8:35 AM
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)?
because if you do, here's a pretty detailed answer about how you can achieve what you are trying to do: https://stackoverflow.com/questions/38679190/query-jsonb-column-containing-array-of-json-objects
g

Gita Alekhya Paul | Quinence

12/06/2021, 8:42 AM
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:
Copy code
{
  "0xsomething1": "SOME_PROPERTY_1",
  "0xsomething2": "SOME_PROPERTY_2"
}
Then I run the following query:
Copy code
const { data, error } = await supabase
        .from("sources")
        .select(`id, sourceContractAddresses->>${contractAddress}`)
        .neq(
            `sourceContractAddresses->>${contractAddress}`,
            `{ ${contractAddress}: null }`
        );
And it worked perfectly!
Thanks again for your inputs 👍
c

chipilov

12/06/2021, 8:46 AM
no worries. ultimately, it's your call but I am not sure if using values as keys is a good idea
for example, it becomes harder to inspect whether a certain JSON object has a particular shape (e.g. you can no longer check for an address field but you need to check for ALL possible values that are now keys)
g

Gita Alekhya Paul | Quinence

12/06/2021, 8:48 AM
true, but in our use-case it is okay to use the values as keys
c

chipilov

12/06/2021, 8:50 AM
ok, your call
fyi, this question is pretty PostgreSQL-specific (i.e. somewhat independent of Supabase infra, if you disregard the fact that you are constrained by supabase client API/PostgREST), and for such questions I've found that you might get quality help in the PostgreSQL official IRC channel (not that you cannot get quality help here but after all, the guys in the IRC channel are focused specifically on PostgreSQL and its intricacies). just my 2cents