Tracking SQL functions in Hasura

Question

Basically I'm trying to compare two JSONB rows and return a numeric value. But I wanna be able to query for it. I'm not sure whether I should use a custom SQL function, a calculated field, or a Postgres generated column, so I need a bit of advice.

I have a jsonb column for each user that keeps a few hundreds of keys/values as such:

USERS TABLE:
| username     | user_jsonb_column                          |
|-----------------------------------------------------------|
| 'user1'      |  {"key1":"value1", "key2":"value2" ... }   |
|--------------|--------------------------------------------|
| 'user2'      |  {"key2":"value2", "key3":"value3" ... }   |

I am trying to calculate the similarity of the jsonb rows of 2 users with a very simple SQL query as such:

SELECT ROUND ((

SELECT COUNT(*) from (
    SELECT jsonb_each(user_jsonb_column)
    FROM users WHERE username = 'johndoe'
      INTERSECT
    SELECT jsonb_each(user_jsonb_column)
    FROM users WHERE username = 'janedoe'
)::decimal AS SAME_PAIRS

/ --divide it by

SELECT COUNT(*) from (
    SELECT jsonb_object_keys(user_jsonb_column)
    FROM users WHERE username = 'johndoe'
      INTERSECT
    SELECT jsonb_object_keys(user_jsonb_column)
    FROM users WHERE username = 'janedoe'
  ) as SAME_KEYS

) * 100) as similarity_percentage

This is working as intended and gives me the similarity result between 2 json objects as a percentage.

I am trying to turn this into a function so that I can query for the similarity percentage of 2 users as such:

query {
  calculate_similarity_percentage(
    args: {user1: "johndoe", user2: "janedoe"}
  ){
      similarity_percentage_value
  }
}

But I'm stuck at this point because I'm not sure whether I should think in terms of a trackable custom SQL function (which should return SETOF <TABLE> but I need a numeric value), a computed field (which can also return BASE type), or maybe a Postgres generated column in my situation.

I've been reading https://hasura.io/docs/1.0/graphql/core/schema/custom-functions.html and https://hasura.io/docs/1.0/graphql/core/schema/computed-fields.html but I couldn't quite figure out how to approach this, so any kind of help or comment would be appreciated.

---

Update: Yes, as Laurenz Albe pointed out, I am able to create a function like this:

CREATE OR REPLACE FUNCTION public.calculate_similarity_percentage(text, text)
 RETURNS numeric
 LANGUAGE sql
 STABLE
AS $function$

SELECT ROUND(
(select count(*) from (
    SELECT jsonb_each(user_jsonb_column) FROM users WHERE username = $1
    INTERSECT
    SELECT jsonb_each(user_jsonb_column) FROM users WHERE username = $2
    ) as SAME_PAIRS
)::decimal / (
    select count(*) from (
    SELECT jsonb_object_keys(user_jsonb_column) FROM users WHERE username = $1
    INTERSECT
    SELECT jsonb_object_keys(user_jsonb_column) FROM users WHERE username = $2
    ) as SAME_KEYS
    )
    * 100) as similarity_percentage

$function$

Then I can execute this function:

SELECT calculate_similarity_percentage('johndoe','janedoe')

And it returns this without any problem:

similarity_percentage
62

However, I would like Hasura to track this function so that I can query it on graphQL as:

query MyQuery {
  calculate_similarity_percentage(args: {user1: "johndoe", user2: "janedoe"}) {
    similarity_percentage
  }
}

But if I try to track the function above, Hasura says:

**SQL Execution Failed**
in function "calculate_similarity_percentage":
the function "calculate_similarity_percentage" cannot be tracked for the following reasons:
• the function does not return a "COMPOSITE" type
• the function does not return a SETOF
• the function does not return a SETOF table

I have no idea if I can find a workaround and return a numeric value as a "COMPOSITE" or SETOF table.

Answer 1

Here is how I kind of solved my case. But this was not the optimal solution so I'm not accepting this as an answer.

I ended up creating another table like this:

USER_RELATION_TABLE:
| user1_col   | user2_col  |
|--------------------------|
| 'johndoe'   | 'janedoe'  |
|--------------------------|
| 'brad'      | 'angelina' |
|--------------------------|
|     ...     |     ...    |

Then I added a computed field on the relation table with the following function:

CREATE OR REPLACE FUNCTION public.calculate_similarity_percentage(user_relation_row user_relation_table)
 RETURNS numeric
 LANGUAGE sql
 STABLE
AS $function$

SELECT ROUND(
(select count(*) from (
    SELECT jsonb_each(user_jsonb_column) FROM users
    WHERE username = user_relation_row.user1_col
    INTERSECT
    SELECT jsonb_each(user_jsonb_column) FROM users
    WHERE username = user_relation_row.user2_col
    ) as SAME_PAIRS
)::decimal / (
    select count(*) from (
    SELECT jsonb_object_keys(user_jsonb_column) FROM users
    WHERE username = user_relation_row.user1_col
    INTERSECT
    SELECT jsonb_object_keys(user_jsonb_column) FROM users
    WHERE username = user_relation_row.user2_col
    ) as SAME_KEYS
    )
    * 100) as similarity_percentage

$function$

Now I can query it on the graphQL like this:

query MyQuery {
  user_relation_table {
    similarity
  }
}