Using Postgres FK from jsonb with Hasura?

Question

We have foreign keys within a json blob in postgres. We join with these like so:

SELECT f.id, b.id FROM foo AS f
  LEFT JOIN bar AS b ON f.data -> 'baz' ->> 'barId' = text(b.id)

I'm now trying out Hasura to do som graphql queries and I need these as object relationships. In the UI I can only try to manually add relationships with normal columns, not nested json data:

Is it at all possible to get a graphql relationship this way?

user_312873 · Answer

I got the answer in comments, thanks @iamnat. I'll just evolve here with my example for clarity since I still struggled a bit:

Super simple schema and data as such:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE foo
(
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name text,
    data jsonb NOT NULL
);

CREATE TABLE bar
(
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name text,
);

WITH bars AS (
  INSERT INTO bar (name) VALUES ('bar') RETURNING id
)
INSERT INTO foo (name, data) VALUES ('foo', jsonb_build_object('barId', (SELECT id FROM bars)));

I then can create a function for the relationship:

CREATE FUNCTION foo_bar(foo_row foo)
RETURNS SETOF bar AS $$
  SELECT *
  FROM bar
  WHERE text(id) = foo_row.data ->> 'barId'
$$ LANGUAGE sql STABLE;

This I can then use in Hasura as a computed field under "Data" -> foo -> Modify -> Computed fields -> "Add a new computed field". Just give it a name and reference the function in a dropdown:

I can then query:

query MyQuery {
  foo {
    name
    foo_bar {
      name
    }
  }
}

with expected result:

{
  "data": {
    "foo": [
      {
        "name": "foo",
        "foo_bar": [
          {
            "name": "bar"
          }
        ]
      }
    ]
  }
}

Using Postgres FK from jsonb with Hasura?

1 Answers