Document Store

Nucleus includes a built-in document store for schemaless JSON data. Store, query, and index documents alongside your relational tables — no separate database needed.

Document Operations

Insert & Retrieve

-- Insert a document, get back its ID
SELECT DOC_INSERT('{"name": "Alice", "age": 30, "tags": ["admin", "dev"]}');
-- → 1

-- Get document by ID
SELECT DOC_GET(1);
-- → {"name":"Alice","age":30,"tags":["admin","dev"]}

-- Count all documents
SELECT DOC_COUNT();

Query by Containment

Find documents matching a JSON pattern using the @> containment operator:

-- Find all documents where age = 30
SELECT DOC_QUERY('{"age": 30}');
-- → "1"  (comma-separated IDs)

-- Nested containment
SELECT DOC_QUERY('{"tags": ["admin"]}');

Extract Nested Values

-- Get a value at a path
SELECT DOC_PATH(1, 'name');
-- → "Alice"

-- Nested paths
SELECT DOC_PATH(1, 'address', 'city');

JSONB Columns

Store JSON directly in relational tables using the JSONB type:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  metadata JSONB
);

INSERT INTO products (name, metadata)
VALUES ('Widget', '{"color": "blue", "weight": 2.5, "dimensions": {"w": 10, "h": 5}}');

JSONB Operators

PostgreSQL-compatible arrow operators for JSON traversal:

-- Get field as JSONB
SELECT metadata -> 'color' FROM products;
-- → "blue"

-- Get field as text
SELECT metadata ->> 'color' FROM products;
-- → blue

-- Nested path as JSONB
SELECT metadata #> '{dimensions,w}' FROM products;
-- → 10

-- Nested path as text
SELECT metadata #>> '{dimensions,w}' FROM products;
-- → 10

JSONB Functions

Construction

-- Build an object from key-value pairs
SELECT JSON_BUILD_OBJECT('name', 'Alice', 'age', 30);
-- → {"name":"Alice","age":30}

-- Build an array
SELECT JSON_BUILD_ARRAY(1, 'two', true, null);
-- → [1,"two",true,null]

-- Convert any value to JSON
SELECT TO_JSONB(42);

Inspection

-- Get the JSON type
SELECT JSON_TYPEOF('{"a":1}');  -- → "object"
SELECT JSON_TYPEOF('[1,2]');    -- → "array"
SELECT JSON_TYPEOF('"hello"');  -- → "string"

-- Array length
SELECT JSON_ARRAY_LENGTH('[1,2,3]');  -- → 3

-- Object keys
SELECT JSON_OBJECT_KEYS('{"a":1,"b":2}');  -- → ["a","b"]

Transformation

-- Set a value at a path
SELECT JSON_SET('{"a":1}', 'b', '2');
-- → {"a":1,"b":2}

-- Pretty print
SELECT JSON_PRETTY('{"a":1,"b":[2,3]}');

-- Strip null values
SELECT JSON_STRIP_NULLS('{"a":1,"b":null,"c":3}');
-- → {"a":1,"c":3}

Path Extraction

-- Extract nested value as JSONB
SELECT JSON_EXTRACT_PATH('{"a":{"b":{"c":42}}}', 'a', 'b', 'c');
-- → 42

-- Extract as text
SELECT JSON_EXTRACT_PATH_TEXT('{"a":{"b":"hello"}}', 'a', 'b');
-- → hello

GIN Index

GIN (Generalized Inverted Index) accelerates containment queries on JSONB columns:

CREATE INDEX idx_products_metadata ON products USING gin (metadata);

-- This query uses the GIN index
SELECT * FROM products WHERE metadata @> '{"color": "blue"}';

The GIN index extracts all path/value pairs from each document, enabling fast lookups without scanning every row.

Use Cases

• User profiles — Flexible schema for varying user attributes
• Product catalogs — Different products have different fields
• Event logging — Store arbitrary event payloads
• Configuration — Store app settings as JSON
• API responses — Cache and query external API data