How to find text in jsonb in all fields using graphQl query

Question

I have Table in postgresql with two columns id(int) and data(jsonb)

id  | data
---------
int | jsonb

data has undefined format for example

{ id: 0, name: "Sergey", age: 23} or {vehicle: "motorcycle", max_speed: 250, brand: "honda"}

I need to write qraphQl query where response contains data list where find key word in any jsonb fields for example:

query {
  declarations(where: {data: {_ilike: "Sergey"}}) {
    id
    data
  }
}

But at this case I have error: "message": "field '_ilike' not found in type: 'jsonb_comparison_exp'"

Which framework do you use for graphql api implementation? Some frameworks provide `contains` operation: `where: {data: {contains: "Sergey"}}` — Eugene, Jul 10 '23 at 08:59
Then check `_contains` operation: `where: {data: {_contains: "Sergey"}}`. I got it from the docs: https://hasura.io/docs/latest/queries/postgres/query-filters/#jsonb-operators-_contains-_has_key-etc — Eugene, Jul 10 '23 at 09:19
Unfortunately it does not work because $jsonFilter: jsonb must be jsonb, but I need to use string. And in your example you exactly know key, but in my case I can not know about key — Евгений Дружинин, Jul 10 '23 at 10:06

score 0 · Answer 1 · answered Jul 10 '23 at 20:13

I need to use user's function in postgresql.

Create function

CREATE OR REPLACE FUNCTION public.new_function1() RETURNS SETOF declarations LANGUAGE sql STABLE AS $function$ select * from declarations where data @? '$.** ? (@ == "Sergey")'; $function$
Track this function into data base
Use this function in graphQl

query { new_function1() { data } }

1 Answers1