graphql-engine

graphql-engine copied to clipboard

It seems I cannot define a foreign key in the console which would map from a field in a JSONB field to a primary key in another table. Only fields themselves are available, but no way to select a JSONB field.

GraphQL relationships also cannot be defined from a JSONB field. :-(

Hey @mitar

You can accomplish this relationship via computed fields esp https://docs.hasura.io/1.0/graphql/manual/schema/computed-fields.html#table-computed-fields

I created a function like:

CREATE OR REPLACE FUNCTION public.fetch_user_jsons(user_row usertable)
 RETURNS SETOF jsontable
 LANGUAGE sql
 STABLE
AS $function$
  SELECT *
  FROM jsontable j
  WHERE
    j.myjson -> 'mykey' = to_jsonb(user_row.id);
$function$

And then add this as a computed field https://hasura.io/docs/1.0/graphql/manual/schema/computed-fields.html

And then query it just like a relationship:

query {
  user {
    id
    name
    jsonRel {
      myjson
      some_other_column_in_jsontable
    }    
  }
}

So you changed a title of the issue, but my issue was that this is not possible to define in the console/web interface. I cannot define a foreign key in the web interface (allowing to have constraints, like conflict propagation), nor GraphQL relation.

But nevertheless, thank you for this example how to do this manually.

@mitar it is not possible to have foreign keys using a field inside a JSONB field in Postgres

So not directly, but you can construct it manually: define an index on the field, and then define triggers which cascade/delete/prevent changes, like a foreign key would.

Console support for computed fields is in 1.1 (currently beta)

Is the above-mentioned solution going to be the recommended approach for the near term?

@dminkovsky Yes. Since there is no simple way to define "foreign key"-like relationships with JSONB keys in Postgres, computed fields which return SETOF is the best pattern to use here.

There are functional indices you can define on PostgreSQL and you can define triggers to get behavior of foreign keys. It would be great if Hasura could do this out of the box.

Reopening this to allow discussion on @mitar 's suggestions

Having quick, easy, and somewhat error and typo controlled management of JSONB pseudo-foreign key relationships in Hasura Console would be amazing. It opens the door to creating some very flexible CMS functionality using almost all Hasura-native functionality. I could create a CMS where my clients can create their own "file" fields that relate to a file table. Dynamic table fields and CMS-like functionality are possible now but very limited when it comes to joining tables--joins and relationships must be done manually. Having Hasura handle this would make it a customer CMS machine!

I understand the Postgres itself does not support foreign key constraints within JSONB but we don't need that. We could emulate 90% of the functionality with a Hasura abstraction that makes the joins for us. The constraints are missing but user-land can handle that.

@tirumaraiselvan this isnt working for me

I have a table called stages, which has a match column [type jsonb] its an array of UUID's [ "e838be48-46a8-4992-abfa-c179e2114ef0", "32e998b1-8607-4ff1-8fea-33aff1fc8941" ]

then i have a table called matches, which has these corresponding UUID's

CREATE OR REPLACE FUNCTION public.fetch_matches3(stages_row stages)
 RETURNS SETOF matches
 LANGUAGE sql
 STABLE
AS $function$
  SELECT *
  FROM matches j
  WHERE
    j.id IN (SELECT jsonb_array_elements(stages_row.matches))
$function$

error = postgres-error : operator does not exist: uuid = jsonb, i've tried casting it to ::text it works fine here? https://dbfiddle.uk/?rdbms=postgres_12&fiddle=3f25c6a7f5ca2c44fbf4d6ddeb8814fa

Try json_array_elements_text instead.

Also, assuming j.id is of type UUID, cast the result from json_array_elements_text to UUID instead (::UUID).

See this fiddle: https://dbfiddle.uk/23oHsoC_