Блог

По сути, я пытаюсь сравнить две строки JSONB и вернуть числовое значение. Но я хочу иметь возможность запрашивать его. Я не уверен, следует ли мне использовать пользовательскую функцию SQL, вычисляемое поле или столбец, созданный Postgres, поэтому мне нужен небольшой совет.

У меня есть столбец jsonb для каждого пользователя, который хранит несколько сотен ключей / значений как таковых:

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

Я пытаюсь вычислить сходство строк jsonb из 2 пользователей с помощью очень простого SQL-запроса как такового:

 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
  

Это работает по назначению и дает мне результат сходства между 2 объектами json в процентах.

Я пытаюсь превратить это в функцию, чтобы я мог запрашивать процент сходства у 2 пользователей как таковых:

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

Но я застрял на этом этапе, потому что я не уверен, должен ли я думать в терминах a trackable custom SQL function (который должен возвращать SETOF <TABLE> , но мне нужно числовое значение), a computed field (который также может возвращать BASE тип) или, может быть, a Postgres generated column в моей ситуации.

Я читал https://hasura.io/docs/1.0/graphql/core/schema/custom-functions.html и https://hasura.io/docs/1.0/graphql/core/schema/computed-fields.html но я не мог до конца понять, как подойти к этому, поэтому буду признателен за любую помощь или комментарии.

Обновление: Да, как указал Лоренц Альбе, я могу создать такую функцию:

 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$
  

Тогда я могу выполнить эту функцию:

 SELECT calculate_similarity_percentage('johndoe','janedoe')
  

И он возвращает это без каких-либо проблем:

 similarity_percentage
62
  

Однако я хотел бы, чтобы Hasura отслеживала эту функцию, чтобы я мог запрашивать ее в GraphQL как:

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

Но если я попытаюсь track выполнить указанную выше функцию, Хасура скажет:

 **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
  

Я понятия не имею, смогу ли я найти обходной путь и вернуть числовое значение в виде «СОСТАВНОЙ» или таблицы SETOF.

Вот как я решил свой случай. Но это не было оптимальным решением, поэтому я не принимаю это как ответ.

В итоге я создал еще одну таблицу, подобную этой:

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

Затем я добавил вычисляемое поле в таблицу отношений со следующей функцией:

 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$
  

Теперь я могу запросить его в GraphQL следующим образом:

 query MyQuery {
  user_relation_table {
    similarity
  }
}