Subqueries

For a general overview of this endpoint, see the Endpoint Overview page.

Subqueries are a powerful feature of modern SQL. The query endpoint can support subqueries via the "subquery" field in the request body. The format of "subquery" is exactly the same as the full request body and can even support recursive subqueries! Here are some helpful examples that show how to use them.

Concise queries with subqueries

Sometimes you may have a calculation that needs to be aggregated in multiple ways. One option would be to repeat the calculation in each aggregation’s select, but that can lead to lots of repeated JSON. Subqueries can be used to reduce duplicated expressions. In this example, we use a subquery to square the property Home_Value, then aggregate with min, max, and avg without repeating the calculation.

{
  "select": [
    {
      "function": "max",
      "alias": "max",
      "parameters": {
        "property": {
          "alias_ref": "hv_squared"
        }
      }
    },
    {
      "function": "min",
      "alias": "min",
      "parameters": {
        "property": {
          "alias_ref": "hv_squared"
        }
      }
    },
    {
      "function": "avg",
      "alias": "avg",
      "parameters": {
        "property": {
          "alias_ref": "hv_squared"
        }
      }
    }
  ],
  "subquery": {
    "select": [
      {
        "function": "multiply",
        "alias": "hv_squared",
        "parameters": {
          "left": "Home_Value",
          "right": "Home_Value"
        }
      }
    ]
  }
}

This example returns:

{
    "query_result": [
        {
            "avg": 33413668226.974968,
            "max": 17640000000000,
            "min": 0
        }
    ]
}

Subqueries for grouping

Subqueries can also be used to perform operations on grouped data. In this example, we get the count of the inferences in each batch in the subquery, then average those counts.

{
    "select": [
        {
            "function": "avg",
            "alias": "avg_count",
            "parameters": {
                "property": {
                    "alias_ref": "batch_count"
                }
            }
        },
        {
            "function": "count",
            "alias": "total_batches"
        }
    ],
    "subquery": {
        "select": [
            {
                "function": "count",
                "alias": "batch_count"
            },
            {
                "property": "batch_id"
            }
        ],
        "group_by": [
            {
                "property": "batch_id"
            }
        ]
    }
}

This query returns:

{
    "query_result": [
        {
            "avg_count": 5930.2558139534885,
            "total_batches": 86
        }
    ]
}