How to get the match records based on two tables in same indices

Elastic Stack Elasticsearch
1

Hi ,
I need to get the output based on join condition tokenaccess.token with employee table and token access table (like sql join)
please find the sample mapping and data

PUT joinindex/_mapping
{
  "properties": {
    "userid": {
      "type": "nested",
      "properties": {
        "user_id": {
          "type": "integer"
        }
      }
    },
    "tokenuseraccess": {
      "type": "nested",
      "properties": {
        "token": {
          "type": "text"
        }
      }
    },
    "age": {
      "type": "long"
    },
    "email": {
      "type": "text",
      "fields": {
        "keyword": {
          "type": "keyword",
          "ignore_above": 256
        }
      }
    },
    "first_name": {
      "type": "text",
      "fields": {
        "keyword": {
          "type": "keyword",
          "ignore_above": 256
        }
      }
    },
    "gender": {
      "type": "text",
      "fields": {
        "keyword": {
          "type": "keyword",
          "ignore_above": 256
        }
      }
    },
    "id": {
      "type": "long"
    },
    "kilometer": {
      "type": "long"
    },
    "table": {
      "type": "text",
      "fields": {
        "keyword": {
          "type": "keyword",
          "ignore_above": 256
        }
      }
    }
  }
}

PUT joinindex/_doc/1
{
  "userid": {
    "user_id": [
      101,
      109
    ]
  },
  "tokenuseraccess": {
    "token": [
      102,
      103
    ]
  },
  "age": 16,
  "email": "john.doe@example.com",
  "first_name": "John",
  "gender": "Male",
  "id": 1,
  "kilometer": 15000,
  "table": "employee"
}

PUT joinindex/_doc/1
{
  "userid": {
    "user_id": [
      104,
      105
    ]
  },
  "tokenuseraccess": {
    "token": [
      104,
      105
    ]
  },
  "age": 27,
  "email": "test.doe@example.com",
  "first_name": "rajesh",
  "gender": "Male",
  "id": 1,
  "kilometer": 1500000,
  "table": "employee"
}

PUT joinindex/_doc/199
{
   "token": 102,
  "table": "tokenuseraccess"
}
2

Elasticsearch is not a relational database and does not support joins so I would recommend you denormalise the data. I also do not understand your example or what you mean when you refer to tables, as that is not a concept in Elasticsearch.

3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.