Document Queries

Overview

The Document API provides a powerful mechanism for retrieving coherent blocks of data from multiple related dimensions in a single API call. Instead of making separate requests for a master record and its related records, Document Queries aggregate everything into one structured response.

Key Benefits:

✅ Single API call retrieves master + related dimensions
✅ Metadata-driven relationships (no hardcoded joins)
✅ Coherent data (all records in consistent state)
✅ Flexible field selection (include/exclude specific fields)
✅ Dynamic filtering via token-based parameters
✅ Support for nested recursion with depth control

Use Cases:

Product with all barcodes, price lists, and inventory
Order with all line items, payments, and shipping info
Customer with all addresses, contacts, and purchase history
Invoice with all line items, taxes, and payment details

Pre-Defined Documents

Pre-defined documents are stored in the TB_ANAG_DOCDIM00 metadata table and can be reused across the application.

Document Definition Structure

Required Fields:

Field	Description	Example
COD_DOCDIM	Unique document identifier	`SHORT_ARTICLE_INFO`
Description	Document description	`Article with select fields from BCODE and LST`
Source Dim	Master dimension to query	`ART` (Articles)
Key Source Dim	Field used to filter master records	`XART03` (Article Code)
Aggregate Dim	JSON: which related dimensions to include	See below
Aggregate Dim Conditions	JSON: filtering on related dimensions	See below
Microservice	Owning microservice	`mssettings`
Module	Application module	`inventory`
Public	Public (1) or private (0)	`1`

Aggregate Dim JSON Format

The Aggregate Dim field defines which related dimensions to include and which fields from each dimension.

Include Specific Fields:

{
  "BCODE": ["XBCODE03", "XBCODE04"],
  "LST": ["XLST10", "XLST13", "XLST14", "XLST15"]
}

Exclude Specific Fields (! prefix):

{
  "BCODE": ["XBCODE03"],
  "LST": ["!LST_ID", "!OWNER", "!CDATA"]
}

Returns all LST fields EXCEPT LST_ID, OWNER, CDATA.

Empty Object = All Related Dimensions:

{}

Returns ALL related dimensions defined in metadata (TB_OBJECT).

Aggregate Dim Conditions JSON

Dynamic filtering on related dimensions using token-based parameters.

Example:

{
  "LST": [
    "XLST03 = '%codLst%'",
    "XLST25 = '%areaListino%'"
  ]
}

Token Format: %tokenName%

Tokens are replaced with actual values from query parameters
Tokens are required - request fails if missing

Querying Pre-Defined Documents

Endpoint

GET /api/v4/document/{COD_DOCDIM}/{key_value}

Parameters:

{COD_DOCDIM} - Document code from TB_ANAG_DOCDIM00
{key_value} - Value to filter master dimension by Key Source Dim field
Token parameters (required if document definition includes tokens)
Optional parameters (depth, compact, use_public_code, markers, etc.)

Example: SHORT_ARTICLE_INFO Document

Document Definition:

COD_DOCDIM: SHORT_ARTICLE_INFO
Source Dim: ART
Key Source Dim: XART03

Aggregate Dim:

{
  "BCODE": ["XBCODE03"],
  "LST": ["XLST10", "XLST13", "XLST14", "XLST15"]
}

Aggregate Dim Conditions:

{
  "LST": [
    "XLST03 = '%codLst%'",
    "XLST25 = '%areaListino%'"
  ]
}

Request:

GET /api/v4/document/SHORT_ARTICLE_INFO/300100?codLst=1&areaListino=ITA

Response:

[
  {
    "ART_ID": 1234,
    "OWNER": "SYS",
    "CDATA": "20200301082500",
    "TREC": "N",
    "XART01": "2",
    "XART03": "055570",
    "XART20": "Spaghetti alla carbonara",
    "XART25": "9.00",
    "XART53": "rep1",
    "XART57": [
      {
        "XBCODE03": "05595012"
      }
    ],
    "XART58": [
      {
        "XLST10": 9.00,
        "XLST13": 10.99,
        "XLST14": 10.99,
        "XLST15": 12.99
      }
    ]
  }
]

Related dimensions:

XART57 = BCODE records (barcodes)
XART58 = LST records (price lists filtered by codLst=1 and areaListino=ITA)

On-the-Fly Documents

Generate documents dynamically without pre-defining them in TB_ANAG_DOCDIM00.

Endpoint

POST /api/v4/document

Request Body Structure

Single Key:

{
  "dim": "ART",
  "val_key": "055570",
  "related_dim": {
    "BCODE": ["XBCODE03"],
    "LST": ["XLST10", "XLST13"]
  },
  "related_dim_condition": {
    "LST": ["XLST03 = '1'", "XLST25 = 'ITA'"]
  },
  "compact": true,
  "use_public_code": false,
  "center_dett": "allviews"
}

Multiple Keys:

{
  "dim": "ART",
  "list_val_keys": ["055570", "055571", "055572"],
  "related_dim": { ... },
  "compact": false
}

Filter-Based:

{
  "dim": "ART",
  "filter_val_key": "contains(XART20,'pasta') OR equals(XART03,'534508')",
  "related_dim": { ... },
  "$offset": 0,
  "$num_rows": 25
}

Example: Full Article Info On-the-Fly

Request:

POST /api/v4/document
{
  "dim": "ART",
  "val_key": "055570",
  "related_dim": {},
  "compact": false,
  "use_public_code": false,
  "center_dett": "allviews",
  "markers": 0
}

Empty related_dim = include ALL related dimensions from metadata.

Response:

[
  {
    "ART_ID": 0,
    "OWNER": "__OWNER__",
    "CDATA": "__CDATA__",
    "TREC": "N",
    "XART03": "055570",
    "XART20": "Spaghetti alla carbonara",
    "XART21": {
      "IVA_ID": 0,
      "XIVA03": "10",
      "XIVA04": "IVA 10%",
      "XIVA05": "10.00"
    },
    "XART53": {
      "DEPFOOD_ID": 0,
      "XDEPFOOD03": "rep1",
      "XDEPFOOD04": "Reparto 1"
    },
    "XART57": [
      { "XBCODE03": "05595012" }
    ],
    "XART58": [
      { "XLST10": 9.00, "XLST13": 10.99 }
    ]
  }
]

Note: markers=0 replaced IDs with __OWNER__, __CDATA__ placeholders for seed data.

Advanced Parameters

depth

Controls recursion depth for nested related dimensions.

?depth=3

Default: 3 levels
Example: Article → Department → Category → Subcategory (3 levels)
Use Case: Prevent infinite recursion, control response size

dim_occurrences

Maximum times the same dimension can appear at any depth level.

?dim_occurrences=3

Default: 3 occurrences
Purpose: Prevent infinite loops in circular relationships
Example: If ART appears 4 times in recursion tree, 4th occurrence won't be expanded

compact

Controls response structure.

?compact=true   # Master dimension fields only
?compact=false  # Master + all related dimensions expanded

compact=true: Returns only master dimension fields (related dims collapsed to IDs)
compact=false: Returns full nested structure with all related dimension data
Note: Aggregate Dim dimensions always shown (compact doesn't apply to them)

use_public_code

Field names vs public descriptions.

?use_public_code=true   # Use public field descriptions
?use_public_code=false  # Use field codes (XART03, etc.)

Example with use_public_code=true:

{
  "progressivoArticolo": "055570",
  "descrizione": "Spaghetti alla carbonara",
  "PrezzoConsigliato": 9.00,
  "BarcodeAssociati": [
    { "codiceEan": "05595012" }
  ]
}

markers

Replace metadata fields with placeholders for seed data.

?markers=1

Replaces:

ID → 0
OWNER → __OWNER__
CDATA → __CDATA__
LOWNER → __LOWNER__
LDATA → __LDATA__
TIMESTAMP → __TIMESTAMP__

Use Case: Export data for database seeding without environment-specific values.

center_dett

Field visibility control (same as regular queries).

?center_dett=visualizza  # L flag required
?center_dett=dettaglio   # D flag required
?center_dett=allviews    # All fields (no COD_ON_OFF filtering)

debug

Returns generated SQL query.

?debug=true

Response includes:

[
  {
    "metadata": true,
    "sql": "SELECT ... FROM TB_ANAG_ART00 LEFT JOIN TB_ANAG_BCODE00 ..."
  },
  { ...document data... }
]

Pagination

For filter-based document queries.

?$offset=0&$num_rows=25

Returns metadata with pagination info:

[
  {
    "metadata": true,
    "count": 150,
    "page": 1,
    "per_page": 25,
    "total_pages": 6
  },
  { ...documents... }
]

Use Cases

Use Case 1: Product Detail Page

Scenario: Display product with all barcodes, prices, inventory, and images.

Solution: Pre-defined document or on-the-fly with related_dim including BCODE, LST, STOCK, IMAGES.

Benefits:

Single API call (instead of 5 separate calls)
Consistent data snapshot
Reduced latency

Use Case 2: Order Management

Scenario: Retrieve order with all line items, payments, shipments.

Solution:

POST /api/v4/document
{
  "dim": "ORD",
  "val_key": "ORD-2025-001",
  "related_dim": {
    "ORDITEM": [],
    "PAYMENT": [],
    "SHIPMENT": []
  },
  "compact": false
}

Use Case 3: Data Export for Seeding

Scenario: Export production data for test environment seeding.

Solution:

GET /api/v4/document/FULL_ARTICLE_INFO/055570?markers=1&use_public_code=false

Result: Clean data with placeholder IDs suitable for import.

Use Case 4: Mobile App Offline Sync

Scenario: Sync product catalog to mobile app with all related data.

Solution: Filter-based document query with pagination:

POST /api/v4/document
{
  "dim": "ART",
  "filter_val_key": "equals(XART06, 1)",  // Active products only
  "related_dim": {
    "BCODE": ["XBCODE03"],
    "LST": ["XLST10"],
    "IMAGES": ["XIMG_URL"]
  },
  "$offset": 0,
  "$num_rows": 100
}

When to Use Document API vs Regular Queries

Use Document API When:

✅ You need master record + multiple related dimensions ✅ Data must be consistent (same transaction/state) ✅ Performance matters (reduce round trips) ✅ Relationship structure is complex ✅ You're building mobile/offline features

Use Regular Queries When:

✅ You only need master dimension data ✅ Related dimensions are loaded on-demand (lazy loading) ✅ You need maximum flexibility in filtering ✅ You're implementing infinite scroll (paginate master only) ✅ Related data updates independently

Best Practices

✅ DO:

Pre-define frequently used documents:

INSERT INTO TB_ANAG_DOCDIM00 (COD_DOCDIM, COD_DIM, ...)
VALUES ('PRODUCT_DETAIL', 'ART', ...);

Improves maintainability and reusability.

Use compact=true for large datasets:

?compact=true

Reduces response size when you don't need full nested data.

Limit depth for performance:

?depth=2

Prevents excessively deep recursion.

Use pagination for filter-based queries:

{
  "filter_val_key": "...",
  "$offset": 0,
  "$num_rows": 100
}

❌ DON'T:

Don't skip required token parameters:

# ❌ Bad - missing codLst token
GET /api/v4/document/SHORT_ARTICLE_INFO/300100?areaListino=ITA

# ✅ Good
GET /api/v4/document/SHORT_ARTICLE_INFO/300100?codLst=1&areaListino=ITA

Don't set depth too high:

# ❌ Bad - excessive recursion
?depth=10

# ✅ Good - reasonable limit
?depth=3

Don't use Document API for simple queries:

# ❌ Bad - overkill for single dimension
POST /api/v4/document
{ "dim": "ART", "val_key": "123", "related_dim": {} }

# ✅ Good - regular query
GET /api/v4/core/ART/123

Don't mix compact and use_public_code incorrectly:

# ⚠️ Warning - compact=true + use_public_code=true may not provide expected nested structure
?compact=true&use_public_code=true

Common Errors

Error: Missing Token Parameter

Problem:

GET /api/v4/document/SHORT_ARTICLE_INFO/300100

Error:

{
  "status": 500,
  "message": "Bad request!, Missing codLst param"
}

Solution: Include all required token parameters from Aggregate Dim Conditions.

Error: Invalid Dimension in related_dim

Problem:

{
  "dim": "ART",
  "related_dim": {
    "INVALID_DIM": []
  }
}

Error:

{
  "status": 400,
  "message": "Dimension INVALID_DIM not related to ART"
}

Solution: Only include dimensions defined in TB_OBJECT as related to master dimension.

Summary

✅ Document API retrieves master + related dimensions in single call
✅ Pre-defined documents stored in TB_ANAG_DOCDIM00
✅ On-the-fly documents for ad-hoc queries
✅ Token-based dynamic filtering (Aggregate Dim Conditions)
✅ Advanced parameters: depth, dim_occurrences, compact, use_public_code, markers
✅ Use for product details, order management, data export, offline sync
✅ Metadata-driven relationships (no hardcoded joins)

Key Takeaways:

Single API call = better performance + consistent data
Pre-define commonly used documents for reusability
Use compact=true for large responses
Limit depth to prevent excessive recursion
Token parameters are required (not optional)

Query Patterns Overview - Other query operations
Filtering - Filter syntax for filter_val_key
Field Retrieval - center_dett and COD_ON_OFF
Pagination - Paginating document results
Relationships - TB_OBJECT foreign keys
COD_ON_OFF - Field visibility flags

Document Queries

Overview​

Pre-Defined Documents​

Document Definition Structure​

Aggregate Dim JSON Format​

Aggregate Dim Conditions JSON​

Querying Pre-Defined Documents​

Endpoint​

Example: SHORT_ARTICLE_INFO Document​

On-the-Fly Documents​

Endpoint​

Request Body Structure​

Example: Full Article Info On-the-Fly​

Advanced Parameters​

depth​

dim_occurrences​

compact​

use_public_code​

markers​

center_dett​

debug​

Pagination​

Use Cases​

Use Case 1: Product Detail Page​

Use Case 2: Order Management​

Use Case 3: Data Export for Seeding​

Use Case 4: Mobile App Offline Sync​

When to Use Document API vs Regular Queries​

Use Document API When:​

Use Regular Queries When:​

Best Practices​

✅ DO:​

❌ DON'T:​

Common Errors​

Error: Missing Token Parameter​

Error: Invalid Dimension in related_dim​

Summary​

Related Concepts​

Overview

Pre-Defined Documents

Document Definition Structure

Aggregate Dim JSON Format

Aggregate Dim Conditions JSON

Querying Pre-Defined Documents

Endpoint

Example: SHORT_ARTICLE_INFO Document

On-the-Fly Documents

Endpoint

Request Body Structure

Example: Full Article Info On-the-Fly

Advanced Parameters

depth

dim_occurrences

compact

use_public_code

markers

center_dett

debug

Pagination

Use Cases

Use Case 1: Product Detail Page

Use Case 2: Order Management

Use Case 3: Data Export for Seeding

Use Case 4: Mobile App Offline Sync

When to Use Document API vs Regular Queries

Use Document API When:

Use Regular Queries When:

Best Practices

✅ DO:

❌ DON'T:

Common Errors

Error: Missing Token Parameter

Error: Invalid Dimension in related_dim

Summary

Related Concepts