Simplify your database management
Database management doesn't need to be complicated—that's why we've added the JSON DB API to FairCom DB v13. It lets you perform all database operations by posting JSON over HTTPS or WSS. Use simple JSON remote procedure calls to process and return data as JSON. Use JSON to query data. Use JSON to insert, update, and delete data. Use JSON to manage databases, tables, indexes, records, transactions, cursors, and SQL.
What you can do with FairCom DB
- Use JSON, SQL, key–value, and ISAM capabilities over the same data, within the same database server.
- Reduce development time with JSON and copy-paste development.
- Simplify data management, querying, and batch processing.
Use our built-in API Explorer to easily create JSON actions. - Spend less time fighting code and more time delivering features.
What you can do with JSON DB API
FairCom DB v13's new API, JSON DB, lets you quickly build web applications and cloud-based services on your ISAM files using any programming language, such as Java, C#, Javascript, Python, etc. You don't need a driver because JSON DB API uses JSON and HTTP, which all programming languages support.
How does it work?
JSON DB API uses simple JSON commands and automatically maps between ISAM and JSON. For example, you can use JSON commands to create tables, query data, run SQL statements, etc. You can query an ISAM record as a JSON document and save a JSON document into an ISAM record.
What does it do?
JSON DB API manages all database operations:
- Create, alter, delete, list, and describe databases, tables, and indexes.
- Insert, update, and delete records using JSON.
- Run SQL queries and statements and return the results as JSON.
- Use specific JSON DB actions to query data with less code and faster results than SQL:some text
- Look up records by key.
- Tail the most recently inserted data.
- Retrieve all records as quickly as possible.
- Retrieve N records sorted ascending or descending.
- Retrieve sample data by skipping through records.
- Retrieve records matching a partial key.
- Retrieve records between two keys.
- Look up the closest matching record and retrieve its surrounding records.
- Use a cursor to return paginated data efficiently.
- Use a cursor to walk records forward or backward while skipping over data.
JSON DB tutorials
Create Table
{
"action": "createTable",
"params": {
"tableName": "orders",
"fields": [
{ "name": "salesDate", "type": "date" },
{ "name": "totalCost", "type": "money", "scale": 4 },
{ "name": "customer", "type": "JSON", "nullable": false },
{ "name": "items", "type": "JSON", "nullable": false },
{ "name": "notes", "type": "varchar", "length": 65500 }
]
}
}
Create Index
{
"action": "createIndex",
"params": {
"tableName": "orders",
"indexName": "salesDate",
"fields": [ { "name": "salesDate" } ],
"waitToBeLoaded": true
}
}
Run SQL Statements
{
"action": "runSQLStatements",
"params": {
"sqlStatements": [
"CREATE FULLTEXT INDEX orders_customer_fts ON orders(customer)",
"CREATE FULLTEXT INDEX orders_items_fts ON orders(items)"
]
}
}
Insert, Update, & Delete JSON Records
{
"action": "insertRecords",
"params": {
"tableName": "orders",
"sourceData": [
{
"salesDate": "2023-12-04",
"totalCost": 7,
"notes": "No Tax. Yay!",
"customer": {
"name": "George Jetson",
"gender": "M",
"rewardsMember": true
},
"items": [
{
"name": "Whole Milk",
"size": "gallon",
"quantity": 1,
"price": 3.5
},
{
"name": "Chocolate Cookies",
"size": "double pack",
"quantity": 2,
"price": 3.5
}
]
}
]
}
}
Retrieve JSON Using Keys
{
"action": "getRecordsByIds",
"params": {
"tableName": "orders",
"ids": [ 1 ]
}
}
Retrieve JSON Using SQL
{
"action": "getRecordsUsingSQL",
"params": {
"sql": "SELECT * FROM orders WHERE items MATCH 'milk AND cookies';"
}
}
Retrieve JSON Using Specialized JSON Queries
{
"action": "getRecordsByIndex",
"params": {
"tableName": "orders",
"indexName": "salesdate",
"skipRecords": 0,
"maxRecords": 20
}
}
Other Features
Regardless of whether you're running SQL queries or JSON queries, and regardless of how the data is actually stored, it'll be returned as a JSON document.
{
"data": [
{
"changeId": 3014,
"customer": {
"gender": "M",
"name": "George Jetson",
"rewardsMember": true
},
"id": 1,
"items": [
{
"name": "Whole Milk",
"price": 3.5,
"quantity": 1,
"size": "gallon"
},
{
"name": "Chocolate Cookies",
"price": 3.5,
"quantity": 2,
"size": "double pack"
}
],
"notes": "No Tax. Yay!",
"salesDate": "2023-12-04",
"totalCost": 7
}
]
}
In addition, your results can include important metadata.
{
"errorCode": 0,
"errorMessage": "",
"result": {
"moreRecords": false,
"requestedRecordCount": 1,
"returnedRecordCount": 1,
"totalRecordCount": 1,
"dataFormat": "objects",
"binaryFormat": "hex",
"primaryKeyFields": [ "id" ],
"changeIdField": "changeId",
"fields": [
{
"name": "id", "type": "bigint", "length": null, "scale": null,
"defaultValue": null, "nullable": false, "primaryKey": 1,
"autoValue": "incrementOnInsert"
},
{
"name": "changeId", "type": "bigint", "length": null, "scale": null,
"defaultValue": null, "nullable": true, "primaryKey": 0,
"autoValue": "changeId"
},
{
"name": "salesDate", "type": "date", "length": null, "scale": null,
"defaultValue": null, "nullable": true, "primaryKey": 0,
"autoValue": "none"
},
{
"name": "totalCost", "type": "money", "length": 32, "scale": 0,
"defaultValue": null, "nullable": true, "primaryKey": 0,
"autoValue": "none"
},
{
"name": "customer", "type": "json", "length": 0, "scale": null,
"defaultValue": null, "nullable": false, "primaryKey": 0,
"autoValue": "none"
},
{
"name": "items", "type": "json", "length": 0, "scale": null,
"defaultValue": null, "nullable": false, "primaryKey": 0,
"autoValue": "none"
},
{
"name": "notes", "type": "varchar", "length": 65500, "scale": null,
"defaultValue": null, "nullable": true, "primaryKey": 0,
"autoValue": "none"
}
],
"data": [
{
"changeId": 3014,
"customer": {
"gender": "M",
"name": "George Jetson",
"rewardsMember": true
},
"id": 1,
"items": [
{
"name": "Whole Milk",
"price": 3.5,
"quantity": 1,
"size": "gallon"
},
{
"name": "Chocolate Cookies",
"price": 3.5,
"quantity": 2,
"size": "double pack"
}
],
"notes": "No Tax. Yay!",
"salesDate": "2023-12-04",
"totalCost": 7
}
]
},
"requestId": "00000008"
}
Discover the power of FairCom DB
Empower your applications with the FairCom DB database. We have a proven track record of supporting mission-critical systems worldwide for more than 45 years. Whether you need a SQL, JSON, Key–Value, or embedded microservices database, FairCom has you covered. Our full-fledged database does it all, all over the same data. And the best part? We're optimized for low-cost application development, so you can save on DBA costs and improve your Time-to-Market.
If you'd like to experiment with FairCom DB yourself, we encourage you to schedule a demo. Our experts are happy to answer any questions and guide you through what DB can do for your environment. Want more tutorials? Visit our documentation.
