Get data that contains an element | `"key<>":Object` => `"key<>":[Object]` <br /> *key* must be a JSONArray while *Object* cannot be JSON.| ["contactIdList<\>":38710](http://apijson.cn:8080/get/{"User[]":{"count":3,"User":{"contactIdList<\>":38710}}}) <br />In SQL, this would be <br />`json_contains(contactIdList,38710)`. <br />It means find data of the User whose contactList contains 38710.

See if it exists |`"key}{@":{`<br /> &nbsp;&nbsp; `"from":"Table",`<br /> &nbsp;&nbsp; `"Table":{ ... }`<br />`}`<br /><br />}{ means EXISTS.<br /> *key* is the one you want to check. <br />Here is a *Subquery* in it, see specifications below for more information. | ["id}{@":{<br /> &nbsp;&nbsp; "from":"Comment",<br /> &nbsp;&nbsp; "Comment":{<br /> &nbsp;&nbsp; &nbsp;&nbsp; "momentId":15 <br /> &nbsp;&nbsp; }<br />}](http://apijson.cn:8080/get/{"User":{"id}{@":{"from":"Comment","Comment":{"momentId":15}}}})<br /> WHERE EXISTS(SELECT * FROM Comment WHERE momentId=15)

Include functions in parameters | `"key()":"function (key0,key1...)"`<br />This will trigger the back-end <br /> `function(JSONObject request, String key0, String key1...)` <br />to get or testify data.<br /> Use - and + to show the order of priority: analyze key-() > analyze the current object > analyze key() > analyze child object > analyze key+()| ["isPraised()":"isContain(praiseUserIdList,userId)"](http://apijson.cn:8080/get/{"Moment":{"id":301,"isPraised()":"isContain(praiseUserIdList,userId)"}}) <br />This will use function boolean isContain(JSONObject request, String array, String value). In this case, client will get "isPraised":true(In this case, client use function to testify if a user clicked ‘like’ button for a Moment.)

Refer a value | `"key@":"key0/key1/.../refKey"`<br />Use / to show path. The part before the colon is the key that wants to refer. The path after the colon starts with the parent level of the key.| ["Moment":{<br /> &nbsp;&nbsp; "userId":38710<br />},<br />"User":{<br /> &nbsp;&nbsp; "id@":"/Moment/userId"<br />}](http://apijson.cn:8080/get/{"Moment":{"userId":38710},"User":{"id@":"%252FMoment%252FuserId"}})<br /> In this example, the value of id in User refer to the *userId* in *Moment*, which means <br />`User.id = Moment.userId`. <br />After the request is sent, <br />`"id@":"/Moment/userId"` will be `"id":38710`.

Subquery | `"key@":{`<br /> &nbsp;&nbsp; `"range":"ALL",` <br /> &nbsp;&nbsp; `"from":"Table",`<br /> &nbsp;&nbsp; `"Table":{ ... }`<br />`}`<br />*range* can be ALL, ANY.<br />*from* means which table you want to query. <br />It’s very similar to how you query in SQL. <br />You can also use *count*, *join*, etc. | ["id@":{<br /> &nbsp;&nbsp; "from":"Comment",<br /> &nbsp;&nbsp; "Comment":{<br /> &nbsp;&nbsp; &nbsp;&nbsp; "@column":"min(userId)" <br /> &nbsp;&nbsp; }<br />}](http://apijson.cn:8080/get/{"User":{"id@":{"from":"Comment","Comment":{"@column":"min(userId)"}}}})<br /> `WHERE id=(SELECT min(userId) FROM Comment)`.

Fuzzy matching | `"key$":"SQL search expressions"` => `"key$":["SQL search expressions"]`<br />Any SQL search expressions.Eg.%key%(include key), key%(start with key),%k%e%y%(include k, e, y). % means any characters. | ["name$":"%m%"](http://apijson.cn:8080/get/{"User[]":{"count":3,"User":{"name$":"%2525m%2525"}}}) <br />In SQL, it's <br />`name LIKE '%m%'`, <br />meaning that get User with ‘m’ in name.

Regular Expression| `"key~":"regular expression"` => `"key~":["regular expression"]`<br />It can be any regular expressions.Eg. ^[0-9]+$ ，*~ not case sensitive, advanced search is applicable.| ["name~":"^[0-9]+$"](http://apijson.cn:8080/get/{"User[]":{"count":3,"User":{"name~":"^[0-9]%252B$"}}}) <br />In SQL, it's <br />`name REGEXP '^[0-9]+$'`.

Get data in a range| `"key%":"start,end"` => `"key%":["start,end"]`<br />The data type of start and end can only be either Boolean, Number or String. Eg. "2017-01-01,2019-01-01" ，["1,90000", "82001,100000"]. It's used for getting data from a specific time range. | ["date%":"2017-10-01,2018-10-01"](http://apijson.cn:8080/get/{"User[]":{"count":3,"User":{"date%2525":"2017-10-01,2018-10-01"}}}) <br />In SQL, it's <br />`date BETWEEN '2017-10-01' AND '2018-10-01'`, <br />meaning to get User data that registered between 2017-10-01 and 2018-10-01.

Make an alias | `"name:alias"`<br />this changes name to alias in returning results. It’s applicable to column, tableName, SQL Functions, etc. but only in GET, HEAD requests. | ["@column":"toId:parentId"](http://apijson.cn:8080/get/{"Comment":{"@column":"id,toId:parentId","id":51}}) <br />In SQL, it's <br />`toId AS parentId`. <br />It'll return `parentId` instead of `toId`.

Add / expand an item | `"key+":Object` <br /> The type of Object is decided by *key*. Types can be Number, String, JSONArray. Froms are 82001,"apijson",["url0","url1"] respectively. It’s only applicable to PUT request.| "praiseUserIdList+":[82001]. In SQL, it's <br />`json_insert(praiseUserIdList,82001)`. <br />Add an *id* that praised the Moment.

Delete / decrease an item | `"Key-":Object`<br /> It’s the contrary of "key+" | "balance-":100.00. In SQL, it's <br />`balance = balance - 100.00`, <br />meaning there's 100 less in balance.

Operations | &, \|, ! <br /> They're used in logic operations. It’s the same as AND, OR, NOT in SQL respectively. <br />By default, for the same key, it’s ‘\|’ (OR)operation among conditions; for different keys, the default operation among conditions is ‘&’(AND). <br /> | ① ["id&{}":">80000,<=90000"](http://apijson.cn:8080/head/{"User":{"id&{}":">80000,<=90000"}}) <br />In SQL, it's <br />`id>80000 AND id<=90000`, <br />meaning *id* needs to be id>80000 & id<=90000<br /><br /> ② ["id\|{}":">90000,<=80000"](http://apijson.cn:8080/head/{"User":{"id\|{}":">90000,<=80000"}}) <br />It's the same as "id{}":">90000,<=80000". <br />In SQL, it's <br />`id>80000 OR id<=90000`, <br />meaning that *id* needs to be id>90000 \| id<=80000<br /><br /> ③ ["id!{}":[82001,38710]](http://apijson.cn:8080/head/{"User":{"id!{}":[82001,38710]}}) <br />In SQL, it's <br />`id NOT IN(82001,38710)`, <br />meaning id needs to be ! (id=82001 \| id=38710).

Keywords in an Array: It can be self-defined. | As for `"key":Object`, *key* is the keyword of *{}* in *"[]":{}*. The type of *Object* is up to *key*.<br /><br />① `"count":Integer` It's used to count the number. The default largest number is 100. <br /><br />② `"page":Integer` It’s used for getting data from which page, starting from 0. The default largest number is 100. It’s usually used with COUNT. <br /><br />③ `"query":Integer` Get the number of items that match conditions<br />When to get the object, the integer should be 0; when to get the total number, it’s 1; when both above, it’s 2.<br />You can get the total number with keyword total. It can be referred to other values. <br />Eg. <br />`"total@":"/[]/total"` <br />Put it as the same level of query. <br />*Query* and *total* are used in GET requests just for convenience. Generally, HEAD request is for getting numbers like the total number.<br /><br />④ `"join":"&/Table0/key0@,</Table1/key1@"`<br />Join tables：<br /> "\<" - LEFT JOIN <br /> ">" - RIGHT JOIN <br /> "&" - INNER JOIN <br /> "\|" - FULL JOIN <br /> "!" - OUTTER JOIN <br /> "@" - APP JOIN <br />Where @ APP JOIN is in application layer.It’ll get all the keys in tables that refKeys in result tables are referred to, like refKeys:[value0, value1….]. Then, as the results get data according to `key=$refKey` a number of times (COUNT), it uses key `IN($refKeys)` to put these counts together in just one SQL query, in order to improve the performance.<br /> Other JOIN functions are the same as those in SQL. <br />`"join":"</ViceTable/key@",`<br />`"MainTable":{},`<br />`"ViceTable":{"key@":"/MainTable/refKey"}`<br />will return <br />`MainTable LEFT JOIN ViceTable` <br />`ON ViceTable.key=MainTable.refKey` <br /><br />⑤ `"otherKey":Object` Self-defined keyword other than those that already in the system. It also returns with self-defined keywords.| ① Get User arrays with maximum of 5：<br />["count":5](http://apijson.cn:8080/get/{"[]":{"count":5,"User":{}}})<br /><br /> ② Look into User arrays on page 3. Show 5 of them each page. <br />["count":5,<br />"page":3](http://apijson.cn:8080/get/{"[]":{"count":5,"page":3,"User":{}}})<br /><br /> ③ Get User Arrays and count the total number of Users：<br />["[]":{<br /> &nbsp;&nbsp; "query":2,<br /> &nbsp;&nbsp; "User":{}<br />},<br />"total@":"/[]/total"](http://apijson.cn:8080/get/{"[]":{"query":2,"count":5,"User":{}},"total@":"%252F[]%252Ftotal"})<br />Questions like total page numbers or if there's next page can be solved by total,count,page functions，<br />Total page number: <br/>`int totalPage = Math.ceil(total / count)`<br />If this is the last page: <br />`boolean hasNextPage = total > count*page`<br />If this is the first page: <br />`boolean isFirstPage = page <= 0`<br />If it's the last page: <br />`boolean isLastPage = total <= count*page`<br />... <br /><br /> ④ Moment INNER JOIN User LEFT JOIN Comment：<br />["[]":{<br /> &nbsp;&nbsp; "join": "&/User/id@,\</Comment/momentId@",<br /> &nbsp;&nbsp; "Moment":{},<br /> &nbsp;&nbsp; "User":{<br /> &nbsp;&nbsp;&nbsp;&nbsp; "name~":"t",<br /> &nbsp;&nbsp;&nbsp;&nbsp; "id@": "/Moment/userId"<br /> &nbsp;&nbsp; },<br /> &nbsp;&nbsp; "Comment":{<br /> &nbsp;&nbsp;&nbsp;&nbsp; "momentId@": "/Moment/id"<br /> &nbsp;&nbsp; }<br />}](http://apijson.cn:8080/get/{"[]":{"count":5,"join":"&%252FUser%252Fid@,\<%252FComment%252FmomentId@","Moment":{"@column":"id,userId,content"},"User":{"name~":"t","id@":"%252FMoment%252FuserId","@column":"id,name,head"},"Comment":{"momentId@":"%252FMoment%252Fid","@column":"id,momentId,content"}}})<br /><br /> ⑤ Add the current user to every level：<br />["User":{},<br />"[]":{<br /> &nbsp;&nbsp; "name@":"User/name", //self-defined keyword<br /> &nbsp;&nbsp; "Moment":{}<br />}](http://apijson.cn:8080/get/{"User":{},"[]":{"name@":"User%252Fname","Moment":{}}})

Keywords in Objects: It can be self-defined. | `"@key":Object` @key is the keyword of {} in Table:{}. The type of Object is decided by @key<br /><br />① `"@combine":"&key0,&key1,\|key2,key3,`<br />`!key4,!key5,&key6,key7..."`<br />First, it’ll group data with same operators. Within one group, it operates from left to right. Then it’ll follow the order of & \| ! to do the operation. Different groups are connected with &. So the expression above will be : <br /> (key0 & key1 & key6 & other key) & (key2 \| key3 \| key7) & !(key4 \| key5) <br />\| is optional. <br /><br />② `"@column":"column;function(arg)..."` Return with specific columns.<br /><br />③ `"@order":"column0+,column1-..."` Decide the order of returning results:<br /><br />④ `"@group":"column0,column1..."` How to group data. If @column has declared Table id, this id need to be included in @group. In other situations, at least one of the following needs to be done:<br />1.Group id is declared in @column<br />2.Primary Key of the table is declared in @group.<br/><br/>⑤ `@having":"function0(...)?value0;function1(...)?value1;function2(...)?value2..."` Add conditions on return results with @having. Usually working with@group, it’s declared in @column.<br /><br />⑥ `"@schema":"sys"` Can be set as default setting.<br /><br />⑦ `"@database":"POSTGRESQL"` Get data from a different database.Can be set as default setting.<br /><br />⑧ `"@role":"OWNER"` Get information of the user, including <br />UNKNOWN,LOGIN,CONTACT,CIRCLE,OWNER,ADMIN，<br />Can be set as default setting. <br />You can self-define a new role or rewrite a role. Use`Verifier.verify` etc. to self-define validation methods. <br /><br />⑨ `"@explain":true` Profiling. Can be set as default setting. <br /><br />⑩ `"@otherKey":Object` Self-define keyword | ① Search *Users* that *name* or *tag* contains the letter "a":<br />["name~":"a",<br />"tag~":"a",<br />"@combine":"name~,tag~"](http://apijson.cn:8080/get/{"User[]":{"count":10,"User":{"@column":"id,name,tag","name~":"a","tag~":"a","@combine":"name~,tag~"}}})<br /><br /> ② Only search column id,sex,name and return with the same order:<br />["@column":"id,sex,name"](http://apijson.cn:8080/get/{"User":{"@column":"id,sex,name","id":38710}})<br /><br /> ③ Search Users that have descending order of name and default order of id:<br />["@order":"name-,id"](http://apijson.cn:8080/get/{"[]":{"count":10,"User":{"@column":"name,id","@order":"name-,id"}}})<br /><br /> ④ Search Moment grouped with userId: <br />["@group":"userId,id"](http://apijson.cn:8080/get/{"[]":{"count":10,"Moment":%7B"@column":"userId,id","@group":"userId,id"}}})<br /><br /> ⑤ Search Moments that id equals or less than 100 and group with userId:<br />["@column":"userId;max(id)",<br />"@group":"userId",<br />"@having":"max(id)>=100"](http://apijson.cn:8080/get/{"[]":{"count":10,"Moment":{"@column":"userId%253Bmax(id)","@group":"userId","@having":"max(id)>=100"}}})<br />You can also define the name of the returned function:<br />["@column":"userId;max(id):maxId",<br />"@group":"userId",<br />"@having":"maxId>=100"](http://apijson.cn:8080/get/{"[]":{"count":10,"Moment":{"@column":"userId%253Bmax(id):maxId","@group":"userId","@having":"maxId>=100"}}})<br /><br /> ⑥ Check Users table in sys: <br />["@schema":"sys"](http://apijson.cn:8080/get/{"User":{"@schema":"sys"}})<br /><br /> ⑦ Check Users table in PostgreSQL:<br />["@database":"POSTGRESQL"](http://apijson.cn:8080/get/{"User":{"@database":"POSTGRESQL"}})<br /><br /> ⑧ Check the current user's activity:<br />["@role":"OWNER"](http://apijson.cn:8080/get/{"[]":{"Moment":{"@role":"OWNER"}}})<br /><br /> ⑨ Turn on profiling: <br />["@explain":true](http://apijson.cn:8080/get/{"[]":{"Moment":{"@explain":true}}})<br /><br /> ⑩ Get the No.0 picture from pictureList：<br />["@position":0, //self-defined keyword<br />"firstPicture()":"getFromArray(pictureList,@position)"](http://apijson.cn:8080/get/{"User":{"id":38710,"@position":0,"firstPicture()":"getFromArray(pictureList,@position)"}})