This documentation is for WSO2 Stream Processor 4.2.0. View documentation for the latest release.

All docs This doc
||
Skip to end of metadata
Go to start of metadata

The actions such as inserting, searching, updating, retrieving, and deleting records can be carried out by invoking the  POST/stores/query  REST API. These actions can be performed for tables as well as windows and aggregations. The following sections provide sample commands for each action.

Inserting records

This allows you to insert a new record to the table with the attribute values you define in the select section.

Syntax

select <attribute name>, <attribute name>, ...
insert into <table>;

Sample cURL command

The following cURL command submits a query that inserts a new record with the specified attribute values to the table RoomOccupancyTable.

curl -X POST https://localhost:9443/stores/query -H "content-type: application/json" -u "admin:admin" 
-d '{"appName" : "RoomService", "query" : "select 10 as roomNo, 2 as people insert into RoomOccupancyTable;" }' -k

Retrieving records

This store query retrieves one or more records that match a given condition from a specified table/window/aggregator.

Retrieving records from tables and windows

This is the store query to retrieve records from a table or a window.

Syntax

from <table/window>
<on condition>? 
select <attribute name>, <attribute name>, ...
<group by>? 
<having>? 
<order by>? 
<limit>?

Sample cURL command

The following cURL command submits a query that retrieves room numbers and types of the rooms starting from room no 10, from a table named roomTypeTable.

The roomTypeTable table must be defined in the RoomService Siddhi application.

curl -X POST https://localhost:9443/stores/query -H "content-type: application/json" -u "admin:admin" 
-d '{"appName" : "RoomService", "query" : "from roomTypeTable select roomNo, type on roomNo >= 10; " }' -k

Sample response

The following is a sample response to the sample cURL command given above.

{"records":[
  [10,"single"],
  [11, "triple"],
  [12, "double"]
]}

Retrieving records from aggregations

This is the store query to retrieve records from an aggregation.

Syntax

from <aggregation>  
  <on condition>?
  within <time range> 
  per <time granularity>
select <attribute name>, <attribute name>, ...
  <groupby>?
  <having>?
  <order by>?
  <limit>?

Sample cURL command

The following cURL command submits a query that retrieves average price of a stock.

curl -X POST https://localhost:9443/stores/query -H "content-type: application/json" -u "admin:admin" 
-d '{"appName" : "StockAggregationAnalysis", "query" : "from TradeAggregation on symbol=='FB' within '2018-**-** +05:00' per 'hours' select AGG_TIMESTAMP, symbol, total, avgPrice" }' -k

Sample response

The following is a sample response to the sample cURL command given above.

{"records":[
  [1531180800, 'FB', 10000.0, 250.0],
  [1531184400, 'FB', 11000.0, 260.0],
  [1531188000, 'FB',  9000.0, 240.0]
]}

Updating records

This store query updates selected attributes stored in a specific table based on a given condition.

Syntax

select <attribute name>, <attribute name>, ...?
update <table>
    set <table>.<attribute name> = (<attribute name>|<expression>)?, <table>.<attribute name> = (<attribute name>|<expression>)?, ...
    on <condition>

Sample cURL command

The following cURL command updates the room occupancy for selected records in the table,RoomOccupancyTable The records that are updated are ones of which the roon number is greater than 10. The room occupancy is updated by adding 1 to the existing value of the people attribute.

curl -X POST https://localhost:9443/stores/query -H "content-type: application/json" -u "admin:admin" -d '{"appName" : "RoomService", "query" : "select 10 as roomNumber, 1 as arrival update RoomTypeTable  set RoomTypeTable.people = RoomTypeTable.people + arrival on RoomTypeTable.roomNo == roomNumber;" }' -k

Deleting records

This store query deletes selected records from a specified table.

Syntax

<select>?  
delete <table>  
on <conditional expresssion>

Sample cURL command

The following cURL command submits a query that deletes a record in the table named RoomTypeTable if it has value for the roomNo attribute that matches the value for the roomNumber attribute of the selection that has 10 as the actual value.

curl -X POST https://localhost:9443/stores/query -H "content-type: application/json" -u "admin:admin" 
-d '{"appName" : "RoomService", "query" : "select 10 as roomNumber
delete RoomTypeTable on RoomTypeTable.roomNo == roomNumber;;" }' -k

Inserting/updating records

Syntax

select <attribute name>, <attribute name>, ...
update or insert into <table>
    set <table>.<attribute name> = <expression>, <table>.<attribute name> = <expression>, ...
    on <condition>

Sample cURL command

The following cURL command submits a query that attempts to update selected records in the RoomAssigneeTable table. The records that are selected to be updated are ones with room numbers that match the numbers specified in the select clause. If matching records are not found, it inserts a new record with the values provided in the select clause.

curl -X POST https://localhost:9443/stores/query -H "content-type: application/json" -u "admin:admin" -d '{"appName" : "RoomService", "query" : "select 10 as roomNo, "single" as type, "abc" as assignee update or insert into RoomAssigneeTable  set RoomAssigneeTable.assignee = assignee  on RoomAssigneeTable.roomNo == roomNo;" }' -k

For more information and examples for store queries, see Siddhi Query Guide - Store Query.

  • No labels