Skip to main content

JSON DB concepts

Using the JSON DB API in applications, web services, and automation scripts

FairCom DB is a multi-API database. The same data can simultaneously be accessed as a key-value store, a JSON document database, a SQL database, and ISAM record buffers. No matter how you store data in FairCom DB, you can simultaneously use these APIs to modify and query the same data.

The FairCom JSON DB API uses JSON commands to manage the database and query data. It uses JSON to insert, update, and delete data, and it returns all data as JSON.

Key JSON DB API concepts

  • A database is a security container for multiple tables.

  • A table contains a specific type of record, such as a person's data.

  • A table contains zero or more records (limited only by storage space).

    • Each record is a JSON document.

    • A record contains one or more fields (up to 2500).

      • Each field is a top-level JSON property.

      • Data is stored in the fields of a table.

      • Each field has a specific, predefined SQL type such as JSON, NUMBER, VARCHAR, BINARY, etc.

        • Predefined field types ensure data is consistent for queries and indexes.

        • A JSON field can store any type of data in any structure for flexible data requirements.

        • You control the tradeoffs between predictable query results and flexibility. You may design a table to have one JSON field for complete flexibility or to have a combination of field types to enforce your desired amount of consistency.

        • Each field and property in a JSON field can be indexed and queried using b-tree and full-text indexes.

        • A maximum of 500 fields can be indexed per table.

        • Two or more fields may be indexed together. Each field in the index can have different sort and case-sensitivity settings.

      • The JSON DB API maps JSON types to SQL field types and vice-versa

        • It automatically converts JSON's built-in types into SQL field types and vice-versa.

        • You control how the server maps binary data to JSON. You can specify whether a binary value is encoded as an array of bytes or is encoded as Base64 or Hexadecimal in a JSON string.

      • A JSON field type may contain any JSON type, including an object, array, string, number, true, false, or null.

      • Fixed types make indexing and query results predictable and fast.

      • By default, each table has an "id" field, which is the unique, primary key of the table. You may optionally create a primary key using one or more fields in a table.

Structured Query Language (SQL) is a standardized programming language that is used to manage relational databases and perform various operations on the data in them.

FairCom's SQL engine is ANSI compliant and has almost all SQL features up through the SQL:2003 standard.

FairCom DB is FairCom Corporation's flagship database product. It is optimized for high-speed transaction processing of JSON documents and binary records.

JavaScript Object Notation (JSON) is a simple format for representing data. It requires very little markup and is flexible enough to represent the majority of data structures. It is self-describing, which makes it ideal for schemaless applications and search. It can optionally be paired with a schema to define an expected structure.

SQL WHEREJSON NAVUTF-8UTF-8ODBCODBCODBCbytesfield typefield type