https://supabase.com/ logo
Join Discord
Powered by
This should work: `SELECT * FROM my_table WHERE m...
# sql
s
This should work: 
SELECT * FROM my_table WHERE metadata -> 'company' = '65'
The 
->
essentially tells it to look inside the contents of the column (
metadata
- named before it), and find the property (
company
- named after it) that matches the clause
j
thanks! my query looks like this at the moment:
Copy code
supabase
      .from('subscriptions')
      .select('*, prices(*, products(*))')
      .in('status', ['trialing', 'active']);
how would I apply the equivalent of the SQL command you just supplied? Mind helping?
the jsonb column is located in the subscriptions-table
s
I'm not 100% sure if this will work, but at a guess:
Copy code
JS
supabase
  .from('subscriptions')
  .select('*,company:metadata->company->65, prices(*, products(*))')
  .in('status', ['trialing', 'active']);
j
something like this? 
.eq('metadata->company', '65')
?
s
This page has some more in-depth examples: https://supabase.io/docs/guides/database/json
I'm actually curious if it works - I don't use the supabase JS lib for advanced filtering like this (I prefer PG functions), so I'm not sure if it will work
j
Seems like your example worked, perfect. And If I want to pass the value to filter on dynamically, can I just use template literals!?
s
Yeah - template literals work without any issues, It's just JS from your side, and the PostgREST server is able to figure out what it needs to do to map it correctly on the DB side
j
Hmmm, this dosen't seem to work: `let test = '75'; supabase .from('subscriptions') .select(
*,company:metadata->company->${test}, prices(*, products(*))
) .in('status', ['trialing', 'active']);`
Ah, discord isn't formatting it correctly thou
s
(Triple backtick for code blocks works) Hmmm, if you define 
75
as a number (i.e remove the quotes), does it still complain? You might also want to try 
'"75"'
j
tried it as a number, will try with double quotes
s
I'm not sure if the dashboard quotes numbers as strings, or if your data is a number as a string
j
the company key in the jsonb is a string
hmm, I was wrong from the beginning, it seems like the filter isn't working even with a string
Copy code
supabase
  .from('subscriptions')
  .select('*,company:metadata->company->65, prices(*, products(*))')
  .in('status', ['trialing', 'active']);
s
Maybe your 
.eq()
method will work then
j
Copy code
supabase
      .from('subscriptions')
      .select('*, prices(*, products(*))')
      .in('status', ['trialing', 'active'])
      .eq('metadata->company', '75');
refering to the docs it seems like this sholud work, but it dosen't
I'm refering to this snippet from the docs:
Copy code
const { data, error } = await supabase
  .from('users')
  .select(`
    id, name,
    address->street
  `)
  .eq('address->postcode', 90210)
s
try using 
->>
instead of 
->
j
that seemed to be the trick, very much thank you guys! ❤️
another question regarding the same topic: If I would like to make the same filter work, but on a foreign table, would that be possible? Something like:
Copy code
.from('subscription_items')
      .select('*, subscriptions(metadata) **Here I would like to filter based on metadata->>company')
      .in('status', ['active']);
PreviousNext
Powered by