[SOLVED] SQL JOIN in Redis(FT module or other solutions)

SQL JOIN in Redis(FT module or other solutions)

I want to transfer part of the SQL queries to redis and ran into a problem that redis does not have an analogue of the JOIN functionality

Example in SQL. Got 2 tables:

CREATE TABLE `games` (
    `id` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
    `fname` CHAR(64) NOT NULL DEFAULT '',
    ...
);

CREATE TABLE `log` (
    `id` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
    `fgame_id` CHAR(64) NOT NULL DEFAULT ''
    `finfo` MEDIUMTEXT NOT NULL
    ...
);

Wonna get game name by game id:

SELECT log.fgame_id, games.fname, log.finfo
FROM log
LEFT JOIN games ON games.id = log.fgame_id

In Redis i created index with the same names and similar structure:

FT.CREATE games
  ON HASH
    PREFIX 1 "games:"
  SCHEMA
    id        TAG SORTABLE
    fname     TEXT SORTABLE


FT.CREATE log
  ON HASH
    PREFIX 1 "log:"
  SCHEMA
    id        TAG SORTABLE
    fgame_id  TEXT SORTABLE
    finfo     TEXT NOINDEX

Is it possible to implement SQL JOIN method on REDIS(FT.SEARCH/FT.AGGREGATE)?

Solution

There isn't support to JOIN straight from Redis using Search. Following your example you can use 2x different PREFIX to map different data tables (games: and log:). For example:

FT.CREATE idx ON HASH PREFIX 2 "games:" "log:" SCHEMA "id" as id TAG "text" as text TEXT "price" as price NUMERIC

and adding the data using the shared key:

HSET games:1 id 1000 text IndianaJones
HSET games:2 id 2000 text Starwars

HSET log:1 id 1000 price 199
HSET log:2 id 2000 price 399

finally using FT.AGGREGATE to return the joint table, like in:

FT.AGGREGATE idx "@id:{1000}" LOAD 2 @text @price
1) "1"
2) 1) "text"
   2) "IndianaJones"
3) 1) "price"
   2) "199"

hope that help as a workaround :-)