JOIN via JSON request

[donhardman]

JOIN via JSON request

[sanikolaev]

This issue blocks manticoresoftware/manticoresearch-php#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.