JOIN via JSON request

[donhardman]

Proposal:

We should implement a JOIN operation that can be executed through a JSON request.

First, we should consider the interface implementation.

It may be implemented as follows by adding another system field with the specified values:

{
  "join": [
    {
      "table": "another table",
      "fields": ["source table attribute", "another table attribute"],
      "type": "inner|left|null"
    },
    ...
  ]
}

The example interface is just a sketch of a simple interaction, and we should research and define the interface we will follow before implementing it.

The text is now written in a more confident and natural-sounding manner, with improved grammar and clarity. The original tone and style have been maintained, and the use of simple words makes the text easy to understand. The emoji has also been kept in the response.

Checklist:

^{To be completed by the assignee. Check off tasks that have been completed or are not applicable.}

- [ ] Task estimated - [ ] Specification created, reviewed and approved - [ ] Implementation completed - [ ] Tests developed - [ ] Documentation updated - [ ] Documentation proofread - [ ] Changelog updated - [ ] OpenAPI YAML updated and issue created to rebuild clients

[sanikolaev]

This issue blocks https://github.com/manticoresoftware/manticoresearch-php/issues/210

[sanikolaev]

It's not a big deal to implement it, but we need to prepare the syntax first. Let's discuss it together.

[donhardman]

Here is a suggestion for an interface we can use:

{
  "join": [
    {
      "type": "inner",
      "table": "another",
      "on": [
        {
          "left": {
            "table": "one",
            "field": "id"
          },
          "operator": "=",
          "right": {
            "table": "another",
            "field": "id"
          }
        }
      ]
    }
  ]
}

Explanation of the join syntax:

• The join property is an array that contains one or more join objects.
• Each join object represents a single join operation and has the following properties:
  • type: Specifies the type of join. In the example, it is set to "inner", indicating an inner join. Other possible values could be "left", "right", or "full" for left join, right join, or full outer join, respectively, depending on what Manticore supports.
  • table: Specifies the name of the table to join with.
  • on: An array of join conditions.
  • Each condition object represents a single join condition and has the following properties:
    • left: Represents the left side of the join condition.
    • table: Specifies the table name for the left side of the condition.
    • field: Specifies the field name for the left side of the condition.
    • operator: Specifies the comparison operator for the join condition. In the example, it is set to "=", indicating an equality condition. Other possible operators could be "<", ">", "<=", ">=", etc., depending on what Manticore currently supports.
    • right: Represents the right side of the join condition.
    • table: Specifies the table name for the right side of the condition.
    • field: Specifies the field name for the right side of the condition.

[sanikolaev]

@glookka pls review the proposed syntax, do you have any objections?

[glookka]

1. Is this syntax

         "left": {
           "table": "one",
           "field": "id"
         },
         "operator": "=",
         "right": {
           "table": "another",
           "field": "id"
         }

better than something like this?

"condition": "one.id=another.id"

2. If we keep the abovementioned syntax, we still have range filters in our json queries that use syntax operation names "lt", "gte", "equals". We might want to keep these names consistent.

[sanikolaev]

better than something like this? "condition": "one.id=another.id"

It seems so as the latter is not structured enough. Not the json way.

If we keep the abovementioned syntax, we still have range filters in our json queries that use syntax operation names "lt", "gte", "equals". We might want to keep these names consistent

Good point. Yes, we'd probably better stick to the existing names.