docs/versioned_docs/version-3.0.0-LTS/tooljet-db/querying-tooljet-db.md
Querying the ToolJet database is as easy as querying any other data source on ToolJet. You can use either the GUI or the SQL editor to interact with your data.
Select the GUI mode from the toggle.
Select the table you want to query and the operation from the dropdown, then enter the required parameters for the selected operation.
Click on the Run button to execute the query.
:::info The selected operation should adhere to the column constraints of the selected table. :::
<div style={{textAlign: 'center'}}> </div> <div style={{paddingTop:'24px', paddingBottom:'24px'}}>Available Operations:
This operation returns all the records from the table.
This operation creates a new record in the table. You can create a single record or multiple records at once.
This operation updates a record in the table. You can update a single record or multiple records at once.
This operation deletes a record in the table. You can delete a single record or multiple records at once.
The ToolJet SQL editor allows you to query the ToolJet Database by writing SQL queries, specifically supporting standard SQL syntax for Data Manipulation Language (DML) commands.
DML Commands: You can use the following DML commands to manipulate data:
Restricted Commands:
Example:
SELECT * FROM users WHERE age > 30
When you are creating, updating, or deleting records in a table that has a foreign key constraint, you need to ensure that the foreign key constraint is not violated.
You can join two or more tables in the ToolJet database by using the Join operation.
Inner Join, Left Join, Right Join, and Full Outer Join.= operation is supported for joining tables. If the selected table and the joining table have a foreign key relationship, both the columns will be auto-populated in the On dropdown.The date with time column stores data in the ISO 8601 format. When querying a table with a date with time column, the column is displayed in the ISO 8601 format by default. To display the date with time column in a more readable format in the Table Component, follow these steps:
{{cellValue}} variable contains the ISO 8601 formatted date. Convert it to a Date object using {{new Date(cellValue)}}, then format the Date object to meet your requirements.In ToolJet Database, a column can be set to JSON Data Type. It can be used to store structured data like arrays or nested objects, making it useful for complex data structures such as configurations or logs. To query the JSON Data Type, follow the following steps:
A flat JSON object is a JSON structure where all key-value pairs exist at a single level, without any nesting. Each key is unique within the object, and all values are direct data entries rather than other objects or arrays.
->> before the key, example ->>city.[
{
"id":1,
"json":{
"id":101,
"age":30,
"city":"Los Angeles",
"name":"Alice Johnson",
"email":"[email protected]",
"country":"USA"
}
}
]
A nested JSON object is a JSON structure that contains key-value pairs, where some values are themselves JSON objects or arrays. This creates a hierarchical, multi-level structure with nested layers, which can represent complex relationships between data elements.
-> before each key, example ->user->preferences->settings->notifications->sms->alerts->appointments->cancellations.Note: You can use -> to access nested JSON fields and use ->> to access the text.
[
{
"id": 102,
"name": "Michael Brown",
"age": 25,
"email": "[email protected]",
"user": {
"preference": {
"settings": {
"notification": {
"sms": {
"alert": false
}
}
}
}
}
},
{
"id": 104,
"name": "David Miller",
"age": 35,
"email": "[email protected]",
"user": {
"preference": {
"settings": {
"notification": {
"sms": {
"alert": false
}
}
}
}
}
}
]
:::info If you have any other questions or feedback about ToolJet Database, please reach us out at [email protected] or join our Slack Community :::
</div> </div>