PostgREST JSONB Root Filter ilike

[voronczoff]

So i can convert jsonb field in select (REST) as jsonbfield::text, but i can't do this for WHOLE jsonb field in filter. Only doable for subobjects with jsonbfield->>subobj, but not for the root object. Is there any way for that?

[steve-chavez]

Only doable for subobjects with jsonbfield->>subobj, but not for the root object. Is there any way for that?

@voronczoff Can you elaborate on that? What error do you get?

[wolfgangwalther]

The point is that ->> converts into text, but you can't use that "trick" to convert the top-level jsonb into text when filtering, i.e. there is no ->> in jsonbfield=eq.something.

[steve-chavez]

We've opposed before to cast filters because that invalidates indexes, should we lift that restriction?

Or maybe we can have an special operator for just this case?

[steve-chavez]

If the point of doing ilike on the json is searching for a value on all the keys, maybe we could allow the @? operator with a JSONpath?

select '{"a":"foo","b":"bar"}'::jsonb
       @? '$.keyvalue() ? (@.value == "foo")';
        ?column? 
----------
 t
(1 row)

[voronczoff]

Only doable for subobjects with jsonbfield->>subobj, but not for the root object. Is there any way for that?

@voronczoff Can you elaborate on that? What error do you get?

In following example jsondata is jsonb type in pg.

So this does not work naturally:
http://localhost:3000/events?limit=33&offset=0&select=id,jsondata&jsondata=ilike.*123*
PostgREST returning: {"code":"42883","details":null,"hint":"No operator matches the given name and argument types. You might need to add explicit type casts.","message":"operator does not exist: jsonb ~~* unknown"}

But i can fetch jsondata as escaped text with jsondata::text

http://localhost:3000/events?limit=33&offset=0&select=id,jsondata::text

Though i can not cast jsondata to text and ilike it on the fly. So it is working in Select, but impossible in Filter. As a workaround i have to make an extra calculated field jsondatatext to have this ilikes\contains on the front. Which is not very convenient.

In pg syntax you can't cast smth like jsondata->>'{}' to return text of whole object. This is a no go:

SELECT id, jsondata
FROM events
WHERE jsondata ->> '{}' ilike '%TEST%';

While with other syntax (#>>) you can:

SELECT id, jsondata #> '{}'
FROM events
WHERE jsondata #>> '{}' ilike '%TEST%';

But i did not find a way to make postgREST cast #>> '{}'.

[voronczoff]

If the point of doing ilike on the json is searching for a value on all the keys, maybe we could allow the @? operator with a JSONpath?

select '{"a":"foo","b":"bar"}'::jsonb
       @? '$.keyvalue() ? (@.value == "foo")';
        ?column? 
----------
 t
(1 row)

There is already an operator in pg #>> '{}' that works just fine for both keys and values (same as ::text):

SELECT id, jsondata #> '{}'
FROM events
WHERE jsondata #>> '{}' ilike '%TEST%';

I just dont understand how to enforce it from REST.