JsonDerulo-API-Spec/query/select.md

Select

back to query /// home

Read rows from a table.

Request

{
  "action": "select",
  "table": "items",
  "columns": ["id", "name", "price"],
  "filter": {
    "and": [
      { "column": "status", "op": "=", "value": "active" },
      { "column": "price", "op": ">", "value": 10 }
    ]
  },
  "joins": [
    {
      "table": "categories",
      "on": "items.category_id = categories.id",
      "type": "LEFT"
    }
  ],
  "order_by": [
    { "column": "name", "direction": "ASC" }
  ],
  "limit": 50,
  "offset": 0
}

Fields

Field	Type	Required	Default	Notes
action	string	yes		must be "select"
table	string	yes		target table
columns	array of strings	no	all columns	which columns to return
where	object	no		simple key value filter (see filters)
filter	object	no		structured filter with operators (see filters)
joins	array	no		join other tables (see filters)
order_by	array	no		sort results (see filters)
limit	integer	no	server default	max rows to return
offset	integer	no	0	skip this many rows

Response

{
  "success": true,
  "data": [
    { "id": 1, "name": "Widget", "price": 19.99 },
    { "id": 2, "name": "Gadget", "price": 29.99 }
  ]
}

Behaviors

• if you dont specify columns you get all columns you have read access to
• if you request columns youre not allowed to see they should get silently stripped and a warning should be included so you know json derulo censored you
• limit should be auto applied if you dont set one (from config defaults). if you set one higher than your allowed max it gets capped and a warning tells you
• ownership scoping should be automatically applied based on your permission level (see ownership)
• you need read permission on every joined table in regards to what you want to join obv. otherwise your whole query gets spanked by jsonderulo!