JSON query with key name restrictions, or quoting keys?

[mstade]

Hi!

Last night I was trying to come up with a select query that would let me filter rows based on the value of a json key nested one level deep. The parent key was “exotic” in the sense that it included characters outside of the url safe ones, e.g. urn:a:b:c:d:something. My query was:

const { data, error } = await supabase
  .from('mytable')
  .select('id')
  .eq('data->a:b:c:d:something->>id', somethingId)

It gave me an error saying the column wasn’t found, which is akin to what I saw with #599, and I figured it was because of the exotic key. Quoting the key in a normal sql query works fine, but I assume Postgrest puts some limitations on the key names, although I’ve been unable to find any docs to explain it. Changing the key to a-b-c-d-something and updating the query to use the same works fine.

I’m ok with changing the key, but for future reference (and search engine results) I wonder, do you know where one might find any docs on what the key name restrictions are, or how to quote the keys such that even dumb keys like mine can be used? :o)

[steve-chavez]

Hey @mstade,

Querying such a column does work through postgrest, but you have to double quote the key:

GET /entities?select=jsonb_col->"a:b:c:d:something"->>id&jsonb_col->"a:b:c:d:something"->>id=eq.3

[{"id":"3"}]

Have you tried to use double quotes(or urlencoded them as %22) inside the supabase-js call? If that doesn't work then it's probably a bug in postgrest-js.

[mstade]

Double quoting seems to work, thank you so much @steve-chavez you've saved me ton of effort! 😊

I'm curious, do you know if this is documented anywhere in the postgrest docs? I'm thinking maybe there are other nuggets of gold I've missed but really should read about.

[steve-chavez]

@mstade Yes, this is documented on: https://postgrest.org/en/v7.0.0/api.html#reserved-characters

[mstade]

Brilliant, thank you so much! 🙏