Query builder - ApiGoat - Backend and API generator

The query builder takes a JSON body to build a specific query for you. The “Query” parameters can be specified for any method, to Select, Update, Delete any specific set of entries.

The query builder is accessible through the POST method for the convenience of putting the parameters in the Body.

When to use

The query builder can be use alone to request data or in combination with Update or Delete actions. Just specify an associative “Query” array.

For example, to update a specific set of data, use the POST method like so:

"query":{
            "select":["id_project"],
            "filter":{
                "project":[["id_project", "1", "NE"]]
            },
            "limit":5,
            "debug":true
        },
{
          "active":"No"
}

Parameters

Parameters as to be passed as JSON in the body of a POST method.

table

Run the query on this particular table.

"table": "table"

select

select is an array with fields for the select query. You can either alias the fields name or not as such:

"select": ["field1", "field2", "foreign_table1.foreign_field"]

aliased:

"select": [["field1", "Alias1"], ["field2", "Alias2"]]

In the case of joined tables, use “ForeignTable.ForeignField” as Field.

filter

To refine a query, the filter property is used to define the exact parameters. Criteria are defined in arrays, per tables, foreign or local. Add ‘%’ caracter wherever needed to produce a like/not like results.

Value can be an array to produce a IN/NOT IN request.

Operand may be one of the following or left empty:

ne — NOT EQUAL, NOT LIKE

lt — LESS THAN

gt — GREATER THAN

Default — EQUAL or LIKE if ‘%’ is present in the Value

"filter": {
    "table1": [["field1", "Value1", "Operand1"], ["Field2", "Value2", "Operand2"]],
    "table2": [["field1", "Value1", "Operand1"]]
}

If table1 is omitted, the model’s table will be used:

"filter": 
    [["field1", "Value1", "Operand1"], ["Field2", "Value2", "Operand2"]]

join

Join foreign tables. It requires a foreign key.

There is two nomenclatures to join foreign table. By default, a LEFT join will be applied. To specify a right join, use the second key of the array.

"join": ["foreign_table1", ["foreign_table2", "RIGHT"]]

order

Order results. Multiple order fields, ASCendant or DESCendant is supported.

"order": [["field1", "ASC|DESC"], ["field2", "ASC|DESC"]]

limit

Set a limit on the number of results

"limit": "10"

page

For paging system, you can define the amount per page, and the page number.

"page": {"max_page": "10", "page": "1"}

Put together

Get the Modifier from the App to produce the list on this website:

{
"query":{
            "select":[["behavior.name","name"],["code","title"],["description","text"],"value","example","type",["behavior_category.name","category_name"]],
            "filter":{
                "behavior":[["group","Free"],["status","Active"]]},
            "join":["behavior_category"],
            "limit":20
        }
}

Update multiple projects, set active to no on id 4 and 5:

{
    "query":{
        "filter":{
            "projects":[["id_projects", ["4", "5"]]]
        }
    },
    "active": "No"
}

More to come!

API