Working with nested objects

LedgyX supports working with nested objects and complex data structures through dot notation. This is especially useful when processing JSON data, API webhooks, and complex parameters.

Dot notation for parameters

Basic syntax

&parameter.nested_field.deep_field::TYPE

Main capabilities:

Access to nested fields via the dot (.)
Typing of the final value via ::TYPE
Multi-level nesting
Automatic parsing of JSON structures

Simple nested objects

-- Access to user fields
SELECT 
    &user.id::NUMBER AS user_id,
    &user.name::TEXT AS user_name,
    &user.email::TEXT AS user_email,
    &user.active::BOOLEAN AS is_active
FROM dictionary.users_temp
TYPE OBJECT;
 
-- Using it in WHERE conditions
SELECT * FROM dictionary.orders
WHERE customer_id = &order.customer.id::NUMBER
  AND status = &order.status::TEXT
TYPE LIST;

Processing Telegram data

Telegram message structure

A Telegram webhook passes a complex data structure that can be easily parsed:

INSERT INTO Dictionary.telegram_messages(
    message_id, update_id, chat_id, chat_type, chat_username, 
    chat_first_name, msg_date, from_id, is_bot, from_username, 
    is_premium, language_code, msg_text
)
SELECT 
    -- Main message fields
    &message.message_id::NUMBER AS message_id,
    &message.update_id::NUMBER AS update_id,
    
    -- Nested chat information
    &message.chat.id::NUMBER AS chat_id,
    &message.chat.type::TEXT AS chat_type,
    &message.chat.username::TEXT AS chat_username,
    &message.chat.first_name::TEXT AS chat_first_name,
    
    -- Timestamp
    &message.date::NUMBER AS chat_date,
    
    -- Nested sender information  
    &message.from.id::NUMBER AS from_id,
    &message.from.is_bot::TEXT AS is_bot,
    &message.from.username::TEXT AS from_username,
    &message.from.is_premium::TEXT AS is_premium,
    &message.from.language_code::TEXT AS language_code,
    
    -- Message content
    &message.text::TEXT AS msg_text
TYPE OBJECT;

Conditional processing of nested data

-- Filtering by chat type and user
SELECT 
    &message.chat.id AS chat_id,
    &message.from.username AS username,
    &message.text AS text
FROM telegram_webhook_data
WHERE &message.chat.type::TEXT = 'private'
  AND &message.from.is_bot::TEXT = 'false'
  AND &message.text::TEXT IS NOT NULL
TYPE LIST;

Processing API data

REST API responses

-- Processing an API response with user data
SELECT 
    &api_response.data.user.id::NUMBER AS user_id,
    &api_response.data.user.profile.name::TEXT AS full_name,
    &api_response.data.user.profile.email::TEXT AS email,
    &api_response.data.user.settings.notifications::BOOLEAN AS notifications_enabled,
    &api_response.data.user.settings.theme::TEXT AS ui_theme,
    &api_response.meta.timestamp::NUMBER AS response_time
TYPE OBJECT;
 
-- Processing arrays in API responses
INSERT INTO dictionary.api_logs (endpoint, status_code, response_time, error_message)
SELECT 
    &request.endpoint::TEXT,
    &response.status::NUMBER,
    &response.timing.total::NUMBER,
    &response.error.message::TEXT
WHERE &response.status::NUMBER >= 400;

Webhook data

-- GitHub webhook events
INSERT INTO dictionary.github_events (
    event_type, repository_name, pusher_name, 
    commit_count, branch_name, timestamp
)
SELECT
    &webhook.event::TEXT AS event_type,
    &webhook.repository.name::TEXT AS repository_name,
    &webhook.pusher.name::TEXT AS pusher_name,
    &webhook.commits.length::NUMBER AS commit_count,
    &webhook.ref::TEXT AS branch_name,
    &webhook.created_at::TEXT AS timestamp
WHERE &webhook.event::TEXT = 'push';

Working with form data

HTML form with nested fields

-- Processing an order form
INSERT INTO dictionary.orders (
    customer_name, customer_email, customer_phone,
    shipping_address, shipping_city, shipping_zip,
    billing_address, billing_city, billing_zip,
    total_amount, currency
)
SELECT
    -- Customer information
    &form.customer.name::TEXT,
    &form.customer.email::TEXT,
    &form.customer.phone::TEXT,
    
    -- Shipping address
    &form.shipping.address::TEXT,
    &form.shipping.city::TEXT,
    &form.shipping.zip::TEXT,
    
    -- Billing address (may differ)
    ISNULL(&form.billing.address::TEXT, &form.shipping.address::TEXT),
    ISNULL(&form.billing.city::TEXT, &form.shipping.city::TEXT),
    ISNULL(&form.billing.zip::TEXT, &form.shipping.zip::TEXT),
    
    -- Financial information
    &form.payment.total::NUMBER,
    &form.payment.currency::TEXT
TYPE OBJECT;

Dynamic form fields

-- Processing arbitrary form fields.
-- Ineron SQL has no CTEs — use a subquery in FROM instead.
INSERT INTO dictionary.form_submissions (submission_id, field_name, field_value, field_type, submitted_at)
SELECT ff.submission_id, ff.field_name, ff.field_value, ff.field_type, ff.submitted_at
FROM (
    SELECT 
        &form.fields.name::TEXT AS field_name,
        &form.fields.value::TEXT AS field_value,
        &form.fields.type::TEXT AS field_type,
        &form.submission.id::NUMBER AS submission_id,
        &form.submission.timestamp::NUMBER AS submitted_at
) AS ff
WHERE ff.field_name IS NOT NULL AND ff.field_value IS NOT NULL;

Processing IoT data

Sensor data

-- Data from IoT devices
INSERT INTO dictionary.sensor_readings (
    device_id, sensor_type, location,
    temperature, humidity, pressure, 
    battery_level, signal_strength, timestamp
)
SELECT
    &iot_data.device.id::TEXT AS device_id,
    &iot_data.device.type::TEXT AS sensor_type,
    &iot_data.device.location.room::TEXT AS location,
    
    -- Sensor readings
    &iot_data.readings.temperature::NUMBER AS temperature,
    &iot_data.readings.humidity::NUMBER AS humidity, 
    &iot_data.readings.pressure::NUMBER AS pressure,
    
    -- System information
    &iot_data.system.battery::NUMBER AS battery_level,
    &iot_data.system.signal::NUMBER AS signal_strength,
    &iot_data.timestamp::NUMBER AS timestamp
TYPE OBJECT;

GPS and geolocation data

-- Location tracking
INSERT INTO dictionary.location_history (
    user_id, latitude, longitude, accuracy,
    speed, bearing, altitude, timestamp
)
SELECT
    &location.user_id::NUMBER,
    &location.coordinates.lat::NUMBER,
    &location.coordinates.lng::NUMBER,
    &location.coordinates.accuracy::NUMBER,
    &location.motion.speed::NUMBER,
    &location.motion.bearing::NUMBER,
    &location.coordinates.altitude::NUMBER,
    &location.timestamp::NUMBER
WHERE &location.coordinates.accuracy::NUMBER < 100; -- Accurate data only

Comprehensive data processing

E-commerce orders

-- Processing an order. Ineron SQL has no CTEs — read the order header from a
-- subquery in FROM (the line items would be inserted with a second statement).
INSERT INTO dictionary.orders (order_id, customer_id, total_amount, status)
SELECT op.order_id, op.customer_id, op.total_amount, 'processing'
FROM (
    SELECT 
        &order.id::NUMBER AS order_id,
        &order.customer.id::NUMBER AS customer_id,
        &order.customer.email::TEXT AS customer_email,
        &order.total.amount::NUMBER AS total_amount,
        &order.total.currency::TEXT AS currency,
        &order.payment.method::TEXT AS payment_method,
        &order.shipping.method::TEXT AS shipping_method,
        &order.created_at::TEXT AS order_date
) AS op;
 
-- The order line items, read from the nested &order.items array
INSERT INTO dictionary.order_items (order_id, product_id, quantity, unit_price, discount_amount)
SELECT 
    &order.id::NUMBER AS order_id,
    item.product_id::NUMBER AS product_id,
    item.quantity::NUMBER AS quantity,
    item.price::NUMBER AS unit_price,
    item.discount::NUMBER AS discount_amount
FROM &order.items AS item(
    product_id INTEGER,
    quantity INTEGER,
    price DECIMAL(10,2),
    discount DECIMAL(10,2)
);

Multi-level configurations

-- Processing application settings.
-- Ineron SQL has no UNION — write one INSERT statement per row.
INSERT INTO dictionary.app_settings (
    user_id, category, setting_name, setting_value, setting_type
)
SELECT 
    &config.user.id::NUMBER AS user_id,
    'ui' AS category,
    'theme' AS setting_name,
    &config.ui.theme::TEXT AS setting_value,
    'string' AS setting_type;
 
INSERT INTO dictionary.app_settings (
    user_id, category, setting_name, setting_value, setting_type
)
SELECT 
    &config.user.id::NUMBER AS user_id,
    'notifications' AS category,
    'email_enabled' AS setting_name,
    &config.notifications.email.enabled::BOOLEAN::TEXT AS setting_value,
    'boolean' AS setting_type;
 
INSERT INTO dictionary.app_settings (
    user_id, category, setting_name, setting_value, setting_type
)
SELECT 
    &config.user.id::NUMBER AS user_id,
    'notifications' AS category,
    'email_frequency' AS setting_name,
    &config.notifications.email.frequency::NUMBER::TEXT AS setting_value,
    'number' AS setting_type;

Alternatively, pass the settings as a JSON array parameter and insert them in one statement with FROM &settings AS s(...).

Error handling and validation

Validating data structure

-- Validating required fields with a single CASE expression (no UNION needed)
SELECT 
    CASE 
        WHEN &user.name::TEXT IS NOT NULL 
         AND &user.email::TEXT IS NOT NULL
         AND &user.id::NUMBER IS NOT NULL
        THEN 'success'
        ELSE 'validation_error'
    END AS status,
    CASE 
        WHEN &user.name::TEXT IS NOT NULL 
         AND &user.email::TEXT IS NOT NULL
         AND &user.id::NUMBER IS NOT NULL
        THEN 'User data valid'
        ELSE 'Missing required fields'
    END AS message
TYPE OBJECT;

Safe processing

-- Protection against SQL injection through typing
SELECT name, email, created_at
FROM dictionary.users
WHERE id = &params.user.id::NUMBER  -- Safe: forced cast to a number
  AND status = &params.user.status::TEXT  -- Safe: text value
  AND created_at > &params.filters.date_from::TIMESTAMP  -- Safe: date
TYPE LIST;

Best practices

1. Field typing

-- ✅ Good: always specify types
SELECT 
    &data.user.id::NUMBER,
    &data.user.name::TEXT,
    &data.user.active::BOOLEAN
FROM user_table;
 
-- ❌ Bad: without typing (unpredictable results)
SELECT 
    &data.user.id,
    &data.user.name,  
    &data.user.active
FROM user_table;

2. Checking for field existence

-- ✅ Good: check for NULL
SELECT 
    ISNULL(&data.user.name::TEXT, 'Unknown') AS user_name,
    ISNULL(&data.user.email::TEXT, 'No email') AS user_email
FROM user_table;

3. Logical grouping

-- ✅ Good: a logical query structure
SELECT 
    -- Main information
    &webhook.event::TEXT AS event_type,
    &webhook.timestamp::NUMBER AS event_time,
    
    -- User information
    &webhook.user.id::NUMBER AS user_id,
    &webhook.user.name::TEXT AS user_name,
    
    -- Repository information
    &webhook.repository.name::TEXT AS repo_name,
    &webhook.repository.owner::TEXT AS repo_owner
FROM github_webhooks;

Related sections:

Advanced syntax - Variables and typing
AI integration - Processing webhook data
INSERT operations - Inserting complex data

Dictionary Creation Blockchain Functions