# Concepts

{% hint style="success" %}
Escape topic names with backticks if they contain non-alpha numeric characters
{% endhint %}

Snapshot queries on streaming data provide answers to a direct question, e.g. The current balance is $10. The query is **active**, the data is **passive**.

### What is a message? <a href="#what-is-a-message" id="what-is-a-message"></a>

A single entry in a Kafka topic is called a message.

The engine considers a message to have four distinct components `key`, `value`, `headers` and `metadata`.

### Facets <a href="#facets" id="facets"></a>

Currently, the Snapshot Engine supports four different facets `_key`, `_value`, `_headers` and `_metadata`; These strings can be used to reference properties of each of the aforementioned message components and build a query that way.

By default, unqualified properties are assumed to belong to the `_value` facet:

```sql
SELECT 
  property
FROM source_topic;
```

In order to reference a different facet, a facet qualifier can be added:

```sql
SELECT 
  _value.valueField,
  _key.keyField,
  _meta.metaField,
  _headers.headerField
FROM source_topic;
```

When more than one sources/topics are specified in a query (like it happens when two topics are joined) a table reference can be added to the selection to fix the ambiguity:

```sql
SELECT 
  users._value.field
FROM users JOIN purchases
```

the same can be done for any of the other facets (`_key`,`_meta`,`_headers`).

Note Using a wildcard selection statement SELECT \* provides only the value component of a message.

Headers are interpreted as a simple mapping of strings to strings. This means that if a header is a JSON, XML or any other structured type, the snapshot engine will still read it as a string value.

### Selecting nested fields <a href="#selecting-nested-fields" id="selecting-nested-fields"></a>

Messages can contain nested elements and embedded arrays. The `.` operator is used to refer to children, and the `[]` operator is used for referring to an element in an array.

You can use a combination of these two operators to access data of any depth.

```sql
SELECT 
    dependencies[0].first_name AS childName
FROM policy_holder
WHERE policyId='100001'
```

You explicitly reference the key, value and metadata.

For the key use `_key`, for the value use `_value`, and for metadata use `_meta`. When there is no prefix, the engine will resolve the field(s) as being part of the message value. For example, the following two queries are identical:

```sql
SELECT 
    amount
FROM payments;

SELECT 
    _value.amount
FROM payments;
```

### Primitive types <a href="#primitive-types" id="primitive-types"></a>

When the key or a value content is a primitive data type use the prefix **only** to address them.

For example, if messages contain a device identifier as the key and the temperature as the value, SQL code would be:

```sql
SELECT 
    _key AS deviceId
  , _value AS temperature
FROM iot_data
```

### Accessing metadata <a href="#accessing-metadata" id="accessing-metadata"></a>

Use the `_meta` keyword to address the metadata. For example:

```sql
SELECT 
    _meta.timestamp AS timestamp
    , _meta.offset AS index
FROM iot_data
```

### Projections and nested aliases <a href="#projections-and-nested-aliases" id="projections-and-nested-aliases"></a>

When projecting a field into a target record, Lenses allows complex structures to be built. This can be done by using a nested alias like below:

```sql
SELECT 
    amount as user.amount
    userId as user.id
FROM payments;
```

The result would be a struct with the following shape:

```sql
{
  "user": {
    "amount" : 10.19,
    "id": 10
  }  
}
```

### **Alias clashes (repeated fields)** <a href="#alias-clashes-repeated-fields" id="alias-clashes-repeated-fields"></a>

When two alias names clash, the snapshot engine does not “override” that field. Lenses will instead generate a new name by appending a unique integer. This means that a query like the following:

```sql
SELECT 
    amount as result.amount,
    amount + 5 as result.amount
FROM payments;
```

will generate a structure like the following:

```sql
{
  "result": {
    "amount" : 10, 
    "amount0": 15
  }  
}
```

### Nested queries <a href="#nested-queries" id="nested-queries"></a>

The tabled query allows you to nest queries. Let us take the query in the previous section and say we are only interested in those entries where there exist more than 1 customer per country.

```sql
SELECT *
FROM (
    SELECT 
        COUNT(*) AS count
        , country
    FROM customer
    GROUP BY country
    )
WHERE count > 1
```

Run the query, and you will only see those entries for which there is more than one person registered per country.

### Functions <a href="#functions" id="functions"></a>

Functions can be used directly.

For example, the `ROUND` function allows you to round numeric functions:

```sql

SELECT
    name 
    , ROUND(quantity * price) AS rounded_total
FROM groceries

/*The output:
Fairtrade Bananas                                   2
Meridian Crunchy Peanut Butter                      3
Green & Black's organic 85% dark chocolate bar      4
Activia fat free cherry yogurts                     6
Green & Blacks Organic Chocolate Ice Cream          8
*/
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.lenses.io/latest/user-guide/using/using-sql-to-query-kafka/concepts.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.