Inconsistent session variable format when using _in VS _contained_in operators

[Li4m-B]

Version Information

Server Version:
CLI Version (for CLI related issue):

Environment

What is the current behaviour?

In our application, we have a session variable set as a JSON array like so:

Which we then use in some of our tables to set permissions using the _contained_in operator, like so:

The value column also being of JSON type, this works great.

However, we recently introduced another table, which contains a simple text column on which we tried to set similar permissions using _in operator. Queries against this table always return no result:

Upon taking a closer look at the documentation, it seems the _in operator expects a session variable using a slightly different format: {123456, 789012, ... } (essentially using {} instead of []).

I tried switching to this format, which works for the second table, but fails when querying the first table with malformed array literal, making it seemingly impossible to mix and match these operators across our schema...

What is the expected behaviour?

Ideally, both _in and _contained_in should accept the same session variable format.

How to reproduce the issue?

1. Set a session variable as a JSON array
2. Set a table permission using {"value":{"_contained_in":"x-hasura-user-groups"}}
3. Set another table permission using {"value":{"_in":"x-hasura-user-groups"}}

Screenshots or Screencast

Please provide any traces or logs that could help here.

Any possible solutions/workarounds you're aware of?

The only way I could think of working around this issue was to create a view and convert the text column to a JSON array with a single field using jsonb_build_array so we could continue to use _contained_in. This is fine for small tables, but far from ideal for larger ones:

CREATE OR REPLACE VIEW public.my_table_view
AS SELECT 
    jsonb_build_array(group) AS group,
    ...
FROM my_table;

Keywords

[jberryman]

Hi, thanks for the clear report.

I think you've correctly identified a limitation that we inherit from postgres; _in and _contained_in simply map to their respective native postgres operations (so the former expects array literal notation like {1,2,3}). I don't think we make any attempt to automatically cast (e.g. varchar to jsonb string) and this isn't planned.

It's not satisfying but maybe you can send a duplicate session variable with the proper type? Otherwise yes I think a view is the other workaround