query/select.md


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67


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!