Ordering JSON column not working

[kiwicopple]

Bug report

Describe the bug

This is failing

const { data: products, error } = await supabase
        .from('products')
        .select('*')
        .order('metadata->index'

Caused by this:

postgrest-js/src/lib/PostgrestTransformBuilder.ts

Line 27 in 43b093d

`"${column}".${ascending ? 'asc' : 'desc'}.${nullsFirst ? 'nullsfirst' : 'nullslast'}`

If we remove the quotes from the "${column}" then it works.

[kiwicopple]

@soedirgo - just need to get an understanding of the "${column}". Is there a requirement for it to be wrapped in quotes?

Perhaps we can just encodeURIComponent(${column})?

[thorwebdev]

To give an example, this is my supabase-js query:

const { data: products, error } = await supabase
        .from('products')
        .select('*, prices(*)')
        .eq('active', true)
        .order('metadata->index')
        .order('unit_amount', { foreignTable: 'prices' });

This is what postgrest-js currently generates:

https://jhomlobmvyuadfdnpnkb.supabase.co/rest/v1/products?select=*%2Cprices%28*%29&active=eq.true&order=%22metadata-%3Eindex%22.asc.nullslast&%22prices%22.order=%22unit_amount%22.asc.nullslast&apikey=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYW5vbiIsImlhdCI6MTYwNTA3MTUwNSwiZXhwIjoxOTIwNjQ3NTA1fQ.FZJkLOldXfPWN7Gtq7J4zFIh5cmnzSzBh_U3Jb0fjhY

Which errors with

{
  "hint": null,
  "details": null,
  "code": "42703", 
  "message": "column products.metadata->index does not exist"
}

when I remove the quotes from all order parts it works:

https://jhomlobmvyuadfdnpnkb.supabase.co/rest/v1/products?select=*%2Cprices%28*%29&active=eq.true&order=metadata-%3Eindex.asc.nullslast&%22prices%22.order=unit_amount.asc.nullslast&apikey=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYW5vbiIsImlhdCI6MTYwNTA3MTUwNSwiZXhwIjoxOTIwNjQ3NTA1fQ.FZJkLOldXfPWN7Gtq7J4zFIh5cmnzSzBh_U3Jb0fjhY

[soedirgo]

Is there a requirement for it to be wrapped in quotes?

This was to anticipate column names like player.name which you'd need to wrap in quotes, but breaking JSONB columns makes this a nonstarter. Will push a fix soon 👍.

[soedirgo]

Also, I don't think postgrest-js supports ordering on multiple columns, doing multiple orders will ignore the previous ones. Will try to make this work as well.

Just realized you were doing the second order on a foreign table, which should still work. Will put off multiple order columns for now...

[github-actions]

🎉 This issue has been resolved in version 0.21.2 🎉

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot 📦🚀