HeliosDB Multi-Model Data Guide

Version: 1.0 Last Updated: 2025-11-30

Quick Start

-- Create table with mixed data types
CREATE TABLE universal_data (
  id SERIAL PRIMARY KEY,
  json_data JSONB,           -- Document data
  xml_data XML,              -- Hierarchical data
  relational_data RECORD,    -- Structured data
  timeseries_data JSONB,     -- Time series data
  graph_data TEXT            -- Graph edges
);

-- Query all data types together
SELECT
  id,
  json_data->>'name' as name,
  (xml_data).nodename as xml_node,
  (relational_data).column1 as rel_value
FROM universal_data;

Data Types

JSONB (Document Data)

-- Store document data
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  data JSONB
);

INSERT INTO documents VALUES (1, '{"title": "Article", "tags": ["tech", "ai"]}');

-- Query
SELECT data->>'title' as title,
       jsonb_array_length(data->'tags') as tag_count
FROM documents;

XML (Hierarchical Data)

-- Store XML
CREATE TABLE xml_data (
  id SERIAL PRIMARY KEY,
  content XML
);

-- Query
SELECT xmlexists('//book[price > 20]' passing by ref content) as has_expensive
FROM xml_data;

Relational (Structured Data)

-- Structured records
CREATE TABLE mixed (
  id SERIAL PRIMARY KEY,
  person RECORD
);

-- Insert
INSERT INTO mixed VALUES
(1, ROW('John', 30, 'Engineer')::person_type);

Array Types

-- Array columns
CREATE TABLE datasets (
  id SERIAL PRIMARY KEY,
  numeric_array INTEGER[],
  text_array TEXT[],
  nested_array INTEGER[][]
);

-- Query
SELECT
  id,
  array_length(numeric_array, 1) as count,
  array_agg(DISTINCT unnest(text_array)) as unique_values
FROM datasets
GROUP BY id;

Multi-Model Queries

Cross-Type Joins

-- Join document and relational data
SELECT
  doc.id,
  doc.data->>'title',
  struct.name,
  struct.age
FROM documents doc
JOIN structured_data struct ON doc.id = struct.id;

Hybrid Aggregation

-- Aggregate mixed data types
SELECT
  (data->>'category')::TEXT as category,
  COUNT(*) as doc_count,
  AVG((data->>'price')::NUMERIC) as avg_price,
  array_agg(DISTINCT tags) as all_tags
FROM json_documents
WHERE (data->>'active')::BOOLEAN = true
GROUP BY category;

Performance

Indexing Mixed Data

-- Index JSON keys
CREATE INDEX idx_doc_title ON documents USING GIN(data);

-- Index XML nodes
CREATE INDEX idx_xml_nodes ON xml_data USING GIN(content);

-- Index array elements
CREATE INDEX idx_array_elem ON datasets USING GIN(numeric_array);

Query Planning

-- Mixed type query optimization
EXPLAIN ANALYZE
SELECT * FROM documents doc
JOIN structured_data s ON doc.id = s.id
WHERE doc.data->>'type' = 'article'
AND s.created_date > NOW() - INTERVAL '30 days';

ETL Patterns

Converting Between Types

-- Document to Relational
CREATE TABLE orders_relational AS
SELECT
  (data->>'order_id')::INTEGER as order_id,
  (data->>'customer')::TEXT as customer_name,
  (data->>'amount')::NUMERIC as amount
FROM json_orders;

-- Relational to Document
CREATE TABLE orders_json AS
SELECT to_jsonb(orders_table);

Best Practices

Choose appropriate type for data structure
Index frequently queried fields
Use schemaless for flexible data
Validate data on insert
Monitor storage usage
Use type conversions wisely

Related Documentation: