Im trying to fetch single entry from my table that contains and JSONB array of objects. Can I match somehow that array to find the desired result?

[
    {
        "chats": [
            { "id":  56789 },
            { "id":  66753 },
        ],
        "id": 999
    },  
    {
        "chats": [
            { "id":  43532 }
        ],
        "id": 999
    }
]

I would like to get the object that matches id 999 and contains in chats -> id: 66753

Tried few approaches but none worked.

I though something link this will work.But no success

let { data, error } = await supabase
  .from('xyz')
  .select('*')
  .eq('id', 999)
  .contains('chats', {id: 66753})

Can it be done ?

Im trying to fetch single entry from my table that contains and JSONB array of objects. Can I match somehow that array to find the desired result?

[
    {
        "chats": [
            { "id":  56789 },
            { "id":  66753 },
        ],
        "id": 999
    },  
    {
        "chats": [
            { "id":  43532 }
        ],
        "id": 999
    }
]

I would like to get the object that matches id 999 and contains in chats -> id: 66753

Tried few approaches but none worked.

I though something link this will work.But no success

let { data, error } = await supabase
  .from('xyz')
  .select('*')
  .eq('id', 999)
  .contains('chats', {id: 66753})

Can it be done ?

• javascript
• node.js
• supabase

1 Answer 1

I believe you need to use the ->> operator when querying JSONB data from supabase as noted here in the docs.

So, if your column with the array of objects is titled jsonb, something to the effect of:

  let { data, error } = await supabase
      .from('xyz')
      .select('*')
      .eq('id:jsonb->>id', 999)
      .contains('chats:jsonb->>chats', ['chats->id: 66753'])

More info can be found on PostgREST docs here