> ## Documentation Index
> Fetch the complete documentation index at: https://docs.fiquela.io/llms.txt
> Use this file to discover all available pages before exploring further.

# FQL syntax

> Reference for File Query Language — a SQL-like string syntax for querying files directly in FiQueLa.

File Query Language (FQL) is a SQL-like string syntax for querying structured files. It mirrors the [fluent API](/querying/fluent-api) feature-for-feature, and every fluent query can be serialized to FQL by casting it to a string.

```php theme={null}
use FQL\Query;

$results = Query\Provider::fql(<<<SQL
    SELECT *
    FROM xml(./products.xml).SHOP.SHOPITEM
    WHERE PRICE <= 200
    ORDER BY PRICE DESC
    LIMIT 10
SQL)->execute()->fetchAll();
```

## Comments

FQL supports comments inside query strings. Comments are ignored during parsing and can be used to annotate or temporarily disable parts of a query.

```sql theme={null}
# Single-line comment (hash)
-- Single-line comment (double dash)

/* Multi-line
   comment */

SELECT id, name
FROM json(products.json).data.products
WHERE price > 100 -- filter cheap products
/* AND stock > 0 */
ORDER BY price DESC
```

* `#` or `--` starts a single-line comment (everything until end of line is ignored)
* `/* ... */` encloses a multi-line comment

## FileQuery syntax

The `FROM`, `JOIN`, and `INTO` clauses use the FileQuery notation to reference files:

```
format(pathToFile[, params]).path.to.data
```

| Part           | Description                                                                                                                                  |
| -------------- | -------------------------------------------------------------------------------------------------------------------------------------------- |
| `format`       | Format identifier: `xml`, `json`, `csv`, `yaml`, `neon`, `ods`, `xls`, `log`, `dir`. If omitted, format is detected from the file extension. |
| `pathToFile`   | Relative or absolute path to the file (unquoted)                                                                                             |
| `params`       | Optional: positional (`"value"`) or named (`key: "value"`). Cannot be mixed                                                                  |
| `path.to.data` | Dot-separated path to the data root within the file                                                                                          |

**Examples:**

```
xml(feed.xml).SHOP.ITEM
csv(data.csv, "windows-1250", ";").*
csv(data.csv, encoding: "windows-1250", delimiter: ";").*
json(data.json).data.users
log(access.log).*
log(access.log, "apache_common").*
log(access.log, format: "apache_common").*
```

**Default parameter values:**

| Format | Parameter   | Default          |
| ------ | ----------- | ---------------- |
| CSV    | `encoding`  | `utf-8`          |
| CSV    | `delimiter` | `,`              |
| CSV    | `useHeader` | `1`              |
| XML    | `encoding`  | `utf-8`          |
| LOG    | `format`    | `nginx_combined` |

## Field path syntax

Field references use dot notation to access nested data. Advanced path features include:

| Syntax               | Description                                         | Example                    |
| -------------------- | --------------------------------------------------- | -------------------------- |
| `a.b.c`              | Standard nested access                              | `brand.code`               |
| `a.b.0.c`            | Indexed access                                      | `items.0.name`             |
| `a.b[].c`            | Iterated access (all array elements)                | `items[].price`            |
| `a.b.*.c`            | Wildcard — expands all keys of an associative array | `attributes.*.value`       |
| `` `key.with.dot` `` | Backtick-escaped key                                | `` `Prod. cena`.`s dph` `` |

Backtick escaping lets you reference keys that contain dots, spaces, or special characters. This syntax works in `SELECT`, `WHERE`, `ORDER BY`, `GROUP BY`, `ON`, and function arguments.

```sql theme={null}
SELECT `items`.`Item`.`Prod. cena`.`s dph` AS priceWithVat,
       items[].name AS allNames
FROM json(data.json).data
```

Scalar functions accept `[]` paths as arguments and operate on the flattened array. For example, `LENGTH(products.product[])` returns the number of elements, and `IMPLODE(products.product[].name, ', ')` joins every nested name into a single string. Backtick-escaped segments work the same way inside function arguments.

<Note>
  Prior to FiQueLa **3.0.2**, scalar functions wrapping a `[]` path returned `null` because the parser bypassed function detection whenever the field contained `[]`. Upgrade to 3.0.2 or later to use array-iterator paths inside scalar functions.
</Note>

## SELECT

```sql theme={null}
SELECT
    [DISTINCT]
    select_expr [AS alias] [, select_expr [AS alias]] ...
    [EXCLUDE excl_expr [, excl_expr] ...]
```

* `select_expr` — column name, function call, or literal. Supports dot notation for nested fields. `*` selects all fields and can be combined with additional expressions (`SELECT *, totalPrice`).
* `AS alias` — optional alias for the expression.
* `EXCLUDE` — removes the listed fields from the output. Fields excluded this way can still be used in `HAVING` conditions.

<Warning>
  Commas are mandatory between expressions in `SELECT`, `GROUP BY`, and `ORDER BY` clauses. For example, `SELECT id, name, price` is valid, but `SELECT id name price` throws an `UnexpectedValueException`. The fluent API (`->select('id, name')`) is unaffected by this requirement.
</Warning>

### Aliased wildcards

When using source aliases (from `FROM ... AS` or `JOIN ... AS`), you can select all fields from a specific aliased source using `alias.*`:

```sql theme={null}
SELECT p.*, o.total_price
FROM json(products.json).data.products AS p
LEFT JOIN xml(orders.xml).orders.order AS o
    ON p.id = o.product_id
```

If two aliased sources share field names and you use `alias.*` for both, a `SelectException` is thrown on ambiguous field conflicts.

**Examples:**

```sql theme={null}
SELECT id, name, brand.code AS brandCode
FROM json(products.json).data.products
```

```sql theme={null}
SELECT DISTINCT category
FROM json(products.json).data.products
```

```sql theme={null}
SELECT
    brand.code AS brandCode,
    COUNT(id) AS productCount
EXCLUDE productCount
FROM json(products.json).data.products
HAVING productCount > 1
```

### Literal values

You can use quoted literal values directly in `SELECT`. Literal strings are automatically cast to their appropriate type:

```sql theme={null}
SELECT
    "my label" AS label,
    "1" AS integerOne,
    "1.5" AS floatValue,
    "true" AS boolTrue,
    "null" AS nullValue
FROM json(products.json).data.products
```

| Quoted value            | Resulting type      |
| ----------------------- | ------------------- |
| `"1"`                   | `int`               |
| `"1.5"`                 | `float`             |
| `"true"` / `"false"`    | `bool`              |
| `"null"`                | `null`              |
| `"2025-05-14 12:00:00"` | `DateTimeImmutable` |
| `"any other text"`      | `string`            |

You can also reference a previously aliased field in a later expression within the same `SELECT`:

```sql theme={null}
SELECT
    EXPLODE(tags, "|") AS tagList,
    tagList[0] AS firstTag
FROM json(products.json).data.products
```

## FROM

```sql theme={null}
FROM file_reference [AS alias]
```

where `file_reference` is a FileQuery string. The path inside the FileQuery sets the data root:

```sql theme={null}
FROM csv(./examples/data/products.csv).*
FROM xml(./feed.xml).SHOP.SHOPITEM
FROM json(./data.json).data.users
```

### FROM aliasing

You can alias the `FROM` source using `AS`. Aliased fields are then accessible via `alias.field` dot notation, which is especially useful when joining multiple sources:

```sql theme={null}
SELECT p.id, p.name, p.price
FROM json(products.json).data.products AS p
WHERE p.price > 100
```

The alias applies to the entire data source. You can combine it with `alias.*` in `SELECT` to select all fields from the aliased source.

## WHERE and HAVING

```sql theme={null}
[WHERE where_condition [AND|OR|XOR where_condition ...]]
[HAVING where_condition [AND|OR|XOR where_condition ...]]
```

Condition syntax:

```sql theme={null}
field_expr comparison_operator value_expr
```

`WHERE` filters rows before aggregation. `HAVING` filters after aggregation.

**Examples:**

```sql theme={null}
WHERE price > 100 AND name LIKE "%widget%"
HAVING total_count > 5 OR total_revenue > 1000
```

FQL supports parentheses for grouping conditions:

```sql theme={null}
WHERE price > 100
  AND (name LIKE "%widget%" OR stock > 0)
```

See [Conditions](/querying/conditions) for all operators.

## GROUP BY

```sql theme={null}
[GROUP BY group_expr [, group_expr] ...]
```

Dot notation is supported for nested fields:

```sql theme={null}
GROUP BY brand.code
GROUP BY category.id, brand.code
```

## ORDER BY

```sql theme={null}
[ORDER BY order_expr [ASC|DESC] [, order_expr [ASC|DESC]] ...]
```

Dot notation is supported for sorting by nested fields:

```sql theme={null}
ORDER BY price DESC
ORDER BY productCount DESC, totalPrice ASC
ORDER BY brand.code ASC
```

## LIMIT and OFFSET

```sql theme={null}
[LIMIT row_count [OFFSET offset]]
[LIMIT row_count, offset]
```

```sql theme={null}
LIMIT 10
LIMIT 20 OFFSET 40
LIMIT 20, 40
```

## EXPLAIN and EXPLAIN ANALYZE

Prepend `EXPLAIN` to return a query execution plan without processing data. Use `EXPLAIN ANALYZE` to execute the query and collect real row counts and timings.

```sql theme={null}
EXPLAIN
SELECT id, name
FROM json(./products.json).data.products
WHERE price > 100
ORDER BY name DESC
LIMIT 10
```

```sql theme={null}
EXPLAIN ANALYZE
SELECT id, name
FROM json(./products.json).data.products
WHERE price > 100
ORDER BY name DESC
LIMIT 10
```

Output columns: `phase`, `rows_in`, `rows_out`, `filtered`, `time_ms`, `duration_pct`, `mem_peak_kb`, `note`.

## WITH (common table expressions)

Declare one or more named subqueries before the main `SELECT`. Each CTE can be referenced by name from any `FROM`, `JOIN`, or `UNION` branch.

```sql theme={null}
WITH name AS (select_statement)
    [, name AS (select_statement)] ...
select_statement
```

* A single `WITH` keyword declares any number of comma-separated CTEs.
* Later CTEs may reference earlier ones (forward-only chaining).
* Duplicate names, `WITH RECURSIVE`, and references to unknown CTE names raise a `ParseException`.
* `EXPLAIN` and `EXPLAIN ANALYZE` accept a leading `WITH` block. `DESCRIBE` cannot be combined with `WITH`.

```sql theme={null}
WITH
    expensive AS (
        SELECT id, name, price, brand.code AS brandCode
        FROM json(./products.json).data.products
        WHERE price > 400
    ),
    expensive_by_brand AS (
        SELECT brandCode, COUNT(id) AS productCount, SUM(price) AS totalPrice
        FROM expensive
        GROUP BY brandCode
    )
SELECT *
FROM expensive_by_brand
ORDER BY totalPrice DESC
```

See [Common table expressions](/advanced/common-table-expressions) for evaluation strategy, fluent API equivalents, and limitations.

## UNION and UNION ALL

```sql theme={null}
select_statement
UNION [ALL]
select_statement
[UNION [ALL] select_statement ...]
```

`UNION` removes duplicate rows. `UNION ALL` keeps all rows. The number of selected columns must match across all queries.

```sql theme={null}
SELECT name, price FROM json(products.json).data.products
WHERE price <= 100
UNION
SELECT name, price FROM json(products.json).data.products
WHERE price >= 400
```

```sql theme={null}
SELECT name, price FROM xml(./feed1.xml).SHOP.ITEM
WHERE price > 100
UNION
SELECT name, price FROM xml(./feed2.xml).SHOP.ITEM
WHERE price > 200
UNION ALL
SELECT name, price FROM xml(./feed3.xml).SHOP.ITEM
```

## INTO

Export query results to a file using the same FileQuery notation:

```sql theme={null}
SELECT name, price
FROM csv(./products.csv).*
INTO json(./exports/products.json).root.items
```

The meaning of the `.query` path in `INTO` depends on the target format:

| Format   | `.query` meaning                          | Example       |
| -------- | ----------------------------------------- | ------------- |
| XML      | `ROOT.ROW` — root element and row element | `.SHOP.ITEM`  |
| JSON     | Nested key path for the resulting array   | `.root.items` |
| XLSX/ODS | `SheetName.StartCell`                     | `.Sheet1.B4`  |
| CSV      | Ignored                                   | —             |
| NDJSON   | Ignored                                   | —             |

<Note>
  Existing target files are not overwritten — an exception is thrown if the file already exists. Missing output directories are created automatically.
</Note>

## DESCRIBE

Use `DESCRIBE` to inspect the schema of a data source. Returns one row per column with type statistics.

```sql theme={null}
DESCRIBE file_reference
```

`file_reference` follows the [FileQuery syntax](#filequery-syntax).

`DESCRIBE` is a standalone statement — it cannot be combined with `SELECT`, `WHERE`, `GROUP BY`, `ORDER BY`, `LIMIT`, `JOIN`, `UNION`, or `EXPLAIN`.

**Examples:**

```sql theme={null}
DESCRIBE json(./examples/data/products.json).data.products
```

```sql theme={null}
DESCRIBE csv(./examples/data/products.csv).*
```

### Output columns

| Column         | Type      | Description                                                                                                                        |
| -------------- | --------- | ---------------------------------------------------------------------------------------------------------------------------------- |
| `column`       | string    | Column name (dot notation for nested objects)                                                                                      |
| `path`         | string\[] | Array of path segments to the column. Useful for correctly evaluating nested fields whose keys contain dots or special characters. |
| `types`        | array     | Map of type name to occurrence count                                                                                               |
| `totalRows`    | int       | Number of non-empty rows for this column                                                                                           |
| `totalTypes`   | int       | Number of distinct types observed                                                                                                  |
| `dominant`     | string    | Most frequent type                                                                                                                 |
| `suspicious`   | bool      | `true` if column has mixed non-empty types (except int+double)                                                                     |
| `confidence`   | float     | Ratio of dominant type occurrences to total (0.0–1.0)                                                                              |
| `completeness` | float     | Ratio of non-empty rows to total rows (0.0–1.0)                                                                                    |
| `constant`     | bool      | `true` if all non-empty values are identical                                                                                       |
| `isEnum`       | bool      | `true` if column has 2–5 unique values                                                                                             |
| `isUnique`     | bool      | `true` if all non-empty values are unique                                                                                          |

## FQL vs fluent API

<CodeGroup>
  ```sql FQL string theme={null}
  SELECT
    brand.code AS brandCode,
    GROUP_CONCAT(id, "/") AS products,
    SUM(price) AS totalPrice,
    COUNT(id) AS productCount
  FROM json(products.json).data.products
  WHERE
    price < 300
    OR price > 400
  GROUP BY brand.code
  ORDER BY productCount DESC
  ```

  ```php Fluent API theme={null}
  use FQL\Query;
  use FQL\Enum\Operator;

  $query = Query\Provider::fromFileQuery('json(products.json).data.products')
      ->select('brand.code')->as('brandCode')
      ->groupConcat('id', '/')->as('products')
      ->sum('price')->as('totalPrice')
      ->count('id')->as('productCount')
      ->where('price', Operator::LESS_THAN, 300)
      ->or('price', Operator::GREATER_THAN, 400)
      ->groupBy('brand.code')
      ->orderBy('productCount')->desc();
  ```
</CodeGroup>