Skip to content
Start free
Start free
Product > Data models

JSON Data

CrateDB supports complex nested JSON payloads with flexible data schema, and indexes all properties automatically for instant querying.

Instant JSON data querying with SQL

Hyper-fast. Queries in milliseconds.

        

INSERT INTO community_areas(areanumber, name, details) VALUES (
   35,
   'DOUGLAS',
   '{
      "description": "The Douglas community area stretches...",
      "population": 20291,
      "zipcodes": [ "60609", "60616", "60653" ],
      "notableresident": {
         "name": "Ida B. Wells",
         "bio": "An American investigative journalist, educator and..."
      }
   }'::OBJECT
)


        
        
        
        
        
        
 
/* Retrieve fields from objects and slice arrays. */
SELECT name, 
       array_slice(details['zipcodes'], 1, 1) as first_zip, 
       details['population'] AS population, 
       details['notableresident']['name'] AS famous_resident 
FROM community_areas 
WHERE areanumber = 35;



        
        
        

+---------+-----------+------------+-----------------+
| name    | first_zip | population | famous_resident |
+---------+-----------+------------+-----------------+
| DOUGLAS | ["60609"] |      20291 | Ida B. Wells    |
+---------+-----------+------------+-----------------+




 

        
        
        
 
/* Insert documents for two Chicago community areas.  Dynamic 
   object schemas allow these two documents to have different
   structures. */
INSERT INTO community_areas(areanumber, name, details) VALUES 
  (
    35,
    'DOUGLAS',
    '{
      "description": "The Douglas community area stretches...",
      "population": 20291,
      "zipcodes": [ "60609", "60616", "60653" ],
      "notableresident": {
        "name": "Ida B. Wells",
        "bio": "An American investigative journalist, educator and..."
      }
    }'::OBJECT
  ),
  (
    41,
    'HYDE PARK',
    '{
      "description": "This community area is home to the Museum of Science...",
      "population": 29456,
      "notableresident": {
        "name": "Muhammad Ali",
        "born": 1942
      }
    }'::OBJECT    
  );

Simple JSON data storage

CrateDB effortlessly handles JSON objects, allowing storage in OBJECT columns. These columns can contain diverse attributes, nesting levels, and arrays of objects, providing the flexibility required for complex multi-model data structures.

cr-quote-image

Automatic indexing

CrateDB's indexing strategy, based on a Lucene index, automatically generates indexes for all attributes regardless of their depth, enabling rapid search capabilities for stored objects and facilitating efficient updates.

cr-quote-image

Simple SQL access to JSON

CrateDB simplifies SQL access to JSON by enabling direct and nested attribute querying, allowing users to navigate and retrieve data seamlessly within JSON structures using native SQL syntax.
cr-quote-image

Dynamic schema and multi-model approach

CrateDB's multi-model approach accommodates diverse data types seamlessly, allowing for the smooth integration of various data structures within the database. As JSON objects evolve and new attributes are added dynamically, CrateDB adjusts the schema automatically. This ensures that all attributes become accessible in SQL queries via the bracket syntax of the form object['attribute']['subattribute'].

cr-quote-image

Powerful full-text search

Alongside JSON support, CrateDB empowers users with robust text search capabilities. This combination of features enables users to perform SQL-based searches within JSON objects efficiently.

cr-quote-image

Fast NoSQL performance

CrateDB executes queries on JSON data (and other types of data) extremely fast, ensuring rapid access and retrieval of JSON objects and complex data structures.
cr-quote-image
Learn more about the product

Demo: Ingesting and querying nested JSON documents with SQL

Watch the tutorial by Georg Traar, Director of Product Management Cloud Organisation, as he shows how to unleash the power of nested data with CrateDB.

Curious to learn more?

Start free
Contact us

Additional resources on JSON data

FAQ

A JSON value can be one of several types: an object, array, number, string, Boolean (true or false), or null. Objects within JSON are collections of key-value pairs, and these keys can contain values of any type, including nested objects or arrays. In CrateDB, JSON objects are treated as container data types, allowing for flexible and efficient data storage.

Storing document/JSON data efficiently requires a flexible database that can handle various attributes and nesting levels. Databases typically define objects as strict, dynamic, or ignored, depending on whether you want to enforce schemas strictly or allow for dynamic schema updates. This flexibility is crucial for managing complex data structures that involve arrays of objects and different nesting levels. In CrateDB, JSON objects can be stored in OBJECT columns, providing the versatility needed for multi-model data storage while maintaining efficient handling and query performance.

When choosing a database for document/JSON data, consider its ability to efficiently store and query JSON documents, indexing capabilities, and support for dynamic schemas. Databases like CrateDB, MongoDB, Couchbase, and ArangoDB excel in these areas, providing flexibility and efficient querying. CrateDB combines traditional SQL strengths with NoSQL's dynamic properties, treating JSON objects as first-class citizens and allowing for full indexing and aggregation of object properties, regardless of their depth and nested structure. Learn more >

Document/JSON data is highly versatile and used across various industries for web applications, APIs, configuration files, data storage, and logging. This format supports flexible, schema-less structures, making it ideal for rapidly changing datasets and complex data models. In CrateDB, JSON data is efficiently stored, indexed, and queried, providing seamless integration with PostgreSQL clients and robust search capabilities.