opensearch dql - procure-project/EU-Contract-Hub GitHub Wiki

Dashboard Query Language (DQL) Guide

Overview

Dashboard Query Language (DQL) is the powerful search syntax used in the EU Contract Hub to find and filter procurement data. DQL enables you to create precise search queries directly in the search bar, helping you locate specific contracts or analyze procurement patterns across the database.

For procurement analysts, DQL offers a significant advantage over basic search by allowing field-specific searches and complex conditions that target exactly the procurement data you need.

Query Language Fundamentals

Basic Query Structure

In its simplest form, DQL lets you search specific fields using a field:value syntax. This allows you to target your searches to particular aspects of procurement contracts.

Field searches: Country:France or Value:>1000000
Text searches: "medical equipment" (quotes for exact phrases)

When you combine multiple terms, the system applies AND logic by default, meaning all conditions must match.

For a complete list of searchable fields, see the Data Fields Reference.

Common Query Types

DQL supports several types of searches to accommodate different procurement analysis needs:

  1. Text Matching

    Use text matching to find contracts containing specific words in fields like Title or Description:

    Title:"medical supplies" finds contracts with this exact phrase in the title

  2. Exact Value Matching

    Perfect for finding contracts with specific codes or classifications:

    CPV:33000000 locates contracts with this exact CPV code

  3. Range Queries

    Useful for filtering contracts by value ranges or date periods:

    Value:[100000 TO 500000] finds contracts valued between €100,000 and €500,000
    Dispatch Date:[2023-01-01 TO 2023-12-31] finds contracts from 2023

Search Features

Text Search

DQL offers several text search capabilities to help you locate relevant procurement documents:

  • Full-text search: Enter terms without a field prefix to search across all fields
  • Field-specific search: Use FieldName:term to search in specific fields (see available text fields)
  • Phrase matching: Use quotes for exact phrases: "hospital equipment"
  • Wildcards: Use * for multiple characters or ? for single characters: medic* matches medical, medicine, etc.
  • Fuzzy matching: Add ~ after a word to find similar terms, helpful for typos: hospital~

Numeric and Date Operations

For procurement analysis involving financial values and timeframes:

  • Greater/less than: Value:>1000000 for contracts over €1 million
  • Between values: Value:[1000000 TO 5000000] for contracts between €1-5 million
  • Date formats: Use ISO format (YYYY-MM-DD) for dates: Dispatch Date:2023-06-30
  • Date ranges: Dispatch Date:[2023-01-01 TO 2023-06-30] for contracts from first half of 2023
  • Relative dates: Dispatch Date:>now-6M for contracts from the last 6 months

For all available numeric and date fields, check the Data Fields Reference.

Boolean Logic

Combine search criteria to create targeted procurement queries:

  • AND operator: medical AND equipment or simply medical equipment
  • OR operator: medical OR healthcare
  • NOT operator: medical NOT equipment or medical -equipment
  • Grouping: Use parentheses for complex logic: (medical OR healthcare) AND equipment

Advanced Query Techniques

Compound Queries

Create sophisticated searches by combining multiple conditions:

Title:"hospital equipment" AND Country:FR AND Dispatch Date:>2023-01-01

This finds French contracts related to hospital equipment published since January 2023.

Nested Queries

For searching within structured data like lots or contractors:

Lots.Title:"medical devices"

This searches specifically within the titles of individual lots, rather than the main contract title.

For all nested field options, refer to the Lots and Nested Fields section of the data reference.

Aggregations

While typing aggregation commands directly in the search bar is not supported, you can apply field-specific aggregations through the visualization builder panel:

  1. Click the "Visualize" button after running your search
  2. Select the aggregation type (terms, range, date histogram, etc.)
  3. Choose the field to aggregate on (e.g., CPV, Value, Country)

EU Contract Hub-Specific Features

Procurement-Specific Fields

The EU Contract Hub provides specialized fields for procurement analysis:

  • CPV codes: Search by Common Procurement Vocabulary codes: CPV:33000000 (see CPV field details)
  • NUTS codes: Filter by geographic region: Contracting Authority.Address.Territorial Unit (NUTS3):FR1 (Paris region) (see NUTS field details)
  • Contract nature: Filter by supplies, services, or works: Contract Nature:"Services"
  • Procurement procedures: Target specific procedures: Procurement Type:"Open"
  • Value thresholds: Use value ranges: Value:[139000 TO *] (above EU threshold)

Multi-language Support

The platform supports searches across multiple EU languages:

  • Original language: Search text in the document's original language: Title:"medicinische geräte"
  • Translated content: Search English translations: Title (Translation):"medical devices"
  • Cross-language matching: The system automatically enhances searches with translations

Geographic Queries

Analyze procurement by location:

  • Country filtering: Country:Spain or Country:(Spain OR Portugal)
  • NUTS regions: Contracting Authority.Address.Territorial Unit (NUTS3):DE1 finds contracts in Baden-Württemberg region
  • Multiple regions: Contracting Authority.Address.Territorial Unit (NUTS3):(FR1 OR ES3) for Paris region or Madrid region
  • Regional aggregations: Use visualization tools to analyze patterns by region

For a complete reference of geographic fields, see the Geographic Fields section.

Common Patterns

These query patterns are particularly useful for procurement analysis:

  1. Contract search by criteria: "medical equipment" AND Value:>1000000 AND Country:DE
  2. Lot analysis: Lots.Title:pharmaceutical AND Lots.Value:>100000
  3. Value-based filtering: Value:[* TO 215000] AND Contract Nature:"Supplies"
  4. Date range analysis: Dispatch Date:[2023-01-01 TO 2023-12-31] AND CPV:33*
  5. Geographic distribution: Procurement Type:"Open" AND Contracting Authority.Address.Territorial Unit (NUTS3):FR*

Examples by Use Case

1. Basic Contract Search

To find medical equipment contracts in the procurement database:

"medical equipment"

This searches all fields for this phrase. To search only titles:

Title:"medical equipment"

2. Complex Procurement Analysis

To analyze high-value medical supply contracts in Germany:

Contract Nature:"Supplies" AND Country:DE AND Value:>1000000 AND CPV:33*

After running this search, use the visualization tools to analyze the distribution by CPV subcategory.

3. Lot-Based Analysis

To find contracts with lots valued above €50,000:

Lots.Value:>50000

This searches within the nested lot structure, identifying contracts with individual lots meeting this value criterion.

Troubleshooting

Common Issues

When using DQL, you might encounter these common problems:

  • Syntax errors: Incorrect use of operators, quotes, or field names
  • Field mismatches: Searching in fields that don't contain the expected data type
  • Missing results: Overly restrictive queries filtering out relevant matches
  • Performance issues: Very broad queries returning too many results
  • Zero results: Errors in field names or search syntax

Solutions

Follow these steps to resolve search problems:

  • Check field names against the Data Fields Reference for correct spelling
  • Verify value formats (dates in YYYY-MM-DD format, numbers without currency symbols)
  • Use wildcards instead of exact matching when appropriate
  • Break complex queries into simpler parts to isolate issues
  • If searching for phrases with special characters, use quotes

Dev-Tools Usage

Query Testing

For advanced users who need to test complex queries:

  1. Access the Dev-Tools console via the "Dev Tools" panel
  2. Write your query in the left panel using the complete JSON syntax
  3. Click the "Run" button to execute and see results in the right panel
  4. Analyze response structure and document matches
  5. Refine your query based on results

Debugging Tools

When troubleshooting complex searches, these tools can help:

  • Request/response inspection: View full API interactions
  • Error messages: Get detailed explanations of syntax issues
  • Performance statistics: See how long queries take to execute
  • Query explanation: Use the explain parameter to understand matching logic
  • Index information: Check mappings to confirm field types and properties

Complete Syntax Dictionary

Field References

  • Simple field: field_name
  • Nested field: parent.child
  • Array element: array_field.N
  • Wildcard: field*

Value Types

  • Text: "quoted string"
  • Numbers: 42, 3.14
  • Dates: 2024-01-01, now-1d
  • Boolean: true, false
  • Null: null

Operators

Comparison

  • Equals: :
  • Greater than: >
  • Less than: <
  • Greater or equal: >=
  • Less or equal: <=
  • Not equal: !:

Logical

  • AND: AND, &&
  • OR: OR, ||
  • NOT: NOT, !

Range

  • Inclusive: [min TO max]
  • Exclusive: {min TO max}
  • Mixed: [min TO max}

Special Characters

  • Wildcards: ? (single), * (multiple)
  • Fuzzy: ~N (where N is edit distance)
  • Proximity: ~N (for phrases)
  • Boost: ^N
  • Escape: \

Query Patterns

Text Matching

# Exact phrase
"exact phrase"

# Fuzzy match
word~2

# Proximity match
"word1 word2"~5

# Wildcard
te?t
test*

Field-Specific Queries

# Single field
field:value

# Multiple values
field:(value1 OR value2)

# Nested field
parent.child:value

# Range
numeric_field:[100 TO 200]
date_field:[2024-01-01 TO 2024-12-31]

Boolean Combinations

# AND combination
field1:value1 AND field2:value2

# OR combination
field1:value1 OR field2:value2

# NOT condition
NOT field:value
field1:value1 AND NOT field2:value2

Grouping

# Simple grouping
(field1:value1 OR field2:value2) AND field3:value3

# Nested grouping
((field1:value1 AND field2:value2) OR field3:value3)

Special Query Types

Regular Expressions

field:/[0-9]{4}/
field:/joh?n(ath[oa]n)/

Exists Queries

_exists_:field_name
NOT _exists_:field_name

Null Checks

field:null
field:*

EU Contract Hub Field Syntax

Common Fields

# Contract identifiers
reference_number:"2024-123456"
title:"medical supplies"

# Numeric values
value:[1000000 TO 5000000]
lots_count:>1

# Dates
dispatch_date:[2024-01-01 TO now]

Nested Fields

# Lot information
lots.title:"medical equipment"
lots.value.amount:>50000

# Contractor details
contractors.name:"Company Ltd"
contractors.address.country:"FR"

Special Fields

# CPV codes
cpv_code:/^331/
cpv_division:33

# Geographic
nuts_code:"FR101"
country:"FR"

For additional guidance on constructing specific queries for your procurement analysis needs, consult the OpenSearch Query Syntax guide or contact the platform support team.