The Guide for Time Series Data Projects is out.

Download now
Skip to content
Get Started
Get Started
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 quotes (
	title, quotation, protagonist
    ) VALUES (
    'Alice in Wonderland',
    { 
    	"words": "Curiouser and curiouser!",
        "length": 3 
     }, 
     {
     	"surname": "Pleasance Liddell",
        "first_name": "Alice",
        "details": {
        	"age": 7,
            "birthday": '1852-05-04T00:00Z'::TIMESTAMPTZ
        } 
     } 
);


        
        
        
 
SELECT
    title AS title,
    protagonist['first_name'] AS name,
    date_format(
        '%D %b %Y',
        'GMT',
        protagonist['details']['birthday']
     ) AS born,
    quotation['words'] AS quote
FROM quotes limit 100;


        
        
        
 
SELECT
    title AS title,
    protagonist['first_name'] AS name,
    date_format(
        '%D %b %Y',
        'GMT',
        protagonist['details']['birthday']
     ) AS born,
    quotation['words'] AS quote
FROM quotes limit 100;


        
        
        
        
        
        
 
INSERT INTO quotes (
    title,
    quotation,
    protagonist
) VALUES 
    (
    'Slaughterhouse-Five',
    {
        "words": "Everything was beautiful, and nothing hurt."
    },
    {
        "surname": "Pilgrim",
        "first_name": "Billy",
        "details": {
            "birthday": '1922-07-04T00:00Z'
        }
    }
    ), 
    (
    'The Complete Tales of Winnie-the-Pooh',
    {
        "words": "How lucky I am to have something that makes saying goodbye so hard."
    },
    {
        "first_name": "Winnie-the-Pooh",
        "details": {
            "birthday": '1926-10-14T00:00Z'
        }
    }
    ), 
    ( 
    'The Complete Tales of Winnie-the-Pooh',
    {
        "words": "I am short, fat, and proud of that."
    },
    {
        "first_name": "Winnie-the-Pooh",
        "details": {
            "birthday": '1926-10-14T00:00Z'
        }
    }
);

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.

Interested?

Get Started
Schedule a meeting

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.