Skip to main content

JSON DB concepts

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

Abstract

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.