Querying JSON columns with the special symbols

[noskovvkirill]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Hi! We are trying to query the data from our jsonb column that has a special symbol @.
We are following the recommended spec by schema-dts

This is an example of code snippet we run:

   const { data, error } = await supabase
          .from('item')
          .select(
            `*, userId, media3Id(*), blockId!inner(*, media3Id!inner(*), consumptionId), newId, customData`,
          )
          .eq('media3Id->data->>schemaOrg2->>@type', schemaPrefix)
          .eq('userId', input.userId)
          .order('createdAt', { ascending: false });
        if (error) {

This is the result that we see

unexpected "@" expecting "-", digit or field name (* or [a..z0..9_$])

Is there anything that we are missing?

To Reproduce

1. Insert data into the jsonb column formatted as schema-dts with @type field
2. Query the data by @type using supabase-js client

[steve-chavez]

Thanks for the report. I can reproduce:

curl 'localhost:3000/bets?data_json->@type=eq.4'

{"code":"PGRST100","details":"unexpected \"@\" expecting \"-\", digit or field name (* or [a..z0..9_$])","hint":null,"message":"\"failed to parse tree path (data_json->@type)\" (line 1, column 12)"}

[steve-chavez]

As a workaround, you could do:

.eq('media3Id->data->>schemaOrg2', {"@type": 1})

I've confirmed this works:

curl 'localhost:3000/jsontest?json->a->b=eq.\{"@type":1\}'

[{"json":{"a": {"b": {"@type": 1}}},"integer":1}]

[laurenceisla]

Wrapping the key in quotations seems to work:

curl "http://localhost:3000/jsonvals?a->>\"@key\"=eq.1"

[{"@key":"1"}]

Doing this should work as another workaround:

.eq('media3Id->data->>schemaOrg2->>"@type"', schemaPrefix)

[mandarini]

I will be closing this issue since it has no activity for more than 2 years.

If you find that the issue persists on the latest version of our libs, feel free to open a new one, ideally with a small reproduction repository.

Thank you for your patience and for contributing to Supabase!

[laurenceisla]

Just to confirm: this was fixed upstream in PostgREST/postgrest#3081.