Data Browser

Advanced Query Mode

Write custom SQL queries using DuckDB syntax for maximum flexibility.

Query Basics

• Syntax: Standard SQL (DuckDB flavor)
• Primary Table: plm_objects
• JSON Access: Use -> or json_extract()
• Full-text search: Use LIKE or ~ (regex)

Basic Query Structure

SELECT column1, column2, ...
FROM plm_objects
WHERE condition1 AND condition2
ORDER BY column1 DESC
LIMIT 100;

JSON Queries

Access nested JSON fields using arrow notation:

-- Extract JSON field
SELECT id, json_data->'$.name' as name
FROM plm_objects
WHERE json_data->'$.status' = 'active';

-- Using json_extract function
SELECT id, json_extract(json_data, '$.attributes.color') as color
FROM plm_objects;

Query Modes

Mode	Speed	Data Source	Best For
redis	Very Fast	In-memory cache	Simple single-type queries
duckdb	Moderate	Analytical database	Complex queries, aggregations, JOINs
auto (default)	Optimized	Intelligent routing	Automatically chooses best backend

Query Tools

• Validate Syntax: Check SQL syntax before executing
• Explain Query: See query execution plan
• Profile Performance: Get detailed timing information
• Insert Template: Quick-start with common query patterns

Database Schema

plm_objects Table

Primary table containing all PLM objects.

Column	Type	Description
id	VARCHAR	Unique object identifier
object_type	VARCHAR	Object type (Style, Product, Material, etc.)
sequence	BIGINT	Message sequence number (for ordering)
timestamp	TIMESTAMP	When the object was received/updated
json_data	JSON	Full object data as JSON
name	VARCHAR	Object display name (virtual column from JSON)
value	VARCHAR	Object short code/value (virtual column from JSON)

plm_cache Table

Redis cache metadata (read-only).

Column	Type	Description
key	VARCHAR	Cache key
value	TEXT	Cached value
ttl	INTEGER	Time to live (seconds)

plm_metadata Table

System metadata and statistics.

Column	Type	Description
key	VARCHAR	Metadata key
value	TEXT	Metadata value
updated_at	TIMESTAMP	Last update time

Query Examples

Copy and paste these examples into Advanced Query mode.

1. Find All Styles

SELECT id, object_type, name, timestamp
FROM plm_objects
WHERE object_type = 'Style'
ORDER BY timestamp DESC
LIMIT 100;

2. Filter by JSON Field

SELECT id, json_data->'$.name' as name, json_data->'$.status' as status
FROM plm_objects
WHERE json_data->'$.status' = 'active'
  AND object_type = 'Product'
LIMIT 50;

3. Date Range Query

SELECT id, object_type, timestamp
FROM plm_objects
WHERE timestamp BETWEEN '2025-01-01' AND '2025-01-31'
ORDER BY timestamp DESC;

4. Count Objects by Type

SELECT object_type, COUNT(*) as count
FROM plm_objects
GROUP BY object_type
ORDER BY count DESC;

5. Find Objects with Specific Attribute

SELECT id, json_data->'$.attributes.season' as season
FROM plm_objects
WHERE json_data->'$.attributes.season' = 'SS25';

6. Text Search (Partial Match)

SELECT id, name, object_type
FROM plm_objects
WHERE name LIKE '%Summer%'
   OR json_data::TEXT LIKE '%Summer%';

7. Complex Aggregation

SELECT
    object_type,
    COUNT(*) as total,
    MIN(timestamp) as first_seen,
    MAX(timestamp) as last_seen
FROM plm_objects
GROUP BY object_type
HAVING COUNT(*) > 10;

8. Join with Expanded References

SELECT
    p.id as product_id,
    p.json_data->'$.name' as product_name,
    s.json_data->'$.name' as style_name
FROM plm_objects p
LEFT JOIN plm_objects s
    ON p.json_data->'$.style_ref' = s.id
WHERE p.object_type = 'Product'
LIMIT 50;

9. Find Recent Changes

SELECT id, object_type, sequence, timestamp
FROM plm_objects
WHERE timestamp > NOW() - INTERVAL '1 day'
ORDER BY timestamp DESC;

10. Export Specific Fields

SELECT
    id,
    json_data->'$.code' as code,
    json_data->'$.name' as name,
    json_data->'$.attributes.color' as color,
    json_data->'$.attributes.size' as size
FROM plm_objects
WHERE object_type = 'SKU';