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

# LAT Data Transformation with JMESPath

export const Ocient = "Ocient®";

<Warning>
  Data Pipelines are now the preferred method for loading data into the {Ocient} System. For details, see [Load Data](/load-data).
</Warning>

Within a transformation each column is configured with a JMESPath expression describing where to extract the column’s value and any transformations to apply to that value.

For information on common JMESPath functions and filtering tools, see the [Standard JMESPATH Functions](/lat-transformation-functions#standard-jmespath-functions) section.

For a list of functions to modify data during loading, see the [LAT Transformation Functions](/lat-transformation-functions) page.

Information on customizing your own transformation functions can be found on the [LAT User-Defined Transformations](/lat-user-defined-transformations) page.

For expressions to select elements from an array matching certain criteria, see the [LAT Record Filtering](/lat-record-filtering) page.

## Notes/Warnings

### JSON Keys with Numbers and Special Characters

You must escape JSON keys that begin with a number or special character in the pipeline configuration.

* For a JSON record: `{ "0_123" : "value" }` transform this record as follows to write "value" to "my\_col" --- `{ "my_col" : "\"0_123\"" }`
* For a JSON record: `{ "a-col" : 123 }` transform this record as follows to write "value" to "my\_col" --- `{ "my_col" : "\"a-col\"" }`

### Case Sensitivity

* The Ocient System normalizes database objects (e.g., table names, column names, schemas) in LAT Transformations according to the same rules as the database unless explicitly escaped with quotes.
  * The Ocient System normalizes all names to lowercase unless explicitly escaped. For example, the transformation: `{ "myCol" : "value" }` normalizes to `mycol` and matches a database column `mycol` that was not escaped at creation time.
  * If you create the database column as an escaped literal such as `"myCol"` then the LAT transformation must be specified as: `{ "\"myCol\"" : "value" }` to correctly map to the column.
* Transformation functions are case sensitive, so represent them by using all lower case letters (e.g., `st_geomfromewkb(my_column)`).

### Literals

* You must escape literal values with a backtick. This applies to numbers, booleans, and empty arrays (otherwise the Ocient System interprets the value as a JSON transformation operator).

Right:

```json JSON theme={null}
{
    "column_1": "`0`",
    "column_2": "`1`",
    "column_3": "`true`",
    "column_4": "`[]`"
}
```

Wrong:

```json JSON theme={null}
{
    "column_1": "0",
    "column_2": "1",
    "column_3": "true",
    "column_4": "[]"
}
```

* You can write string literals with a single quote.

```json JSON theme={null}
 { "column_1": "'example_value'" }
```

### Loading Numbers in Exponential Notation

When you need to load numbers from exponential notation (e.g. `1.20E-05`), you might require a conversion to load them into the target Ocient column or to perform transformations on the data. Use the `to_number` transformation to load this into a column of type `decimal` or to convert to a numeric value for further transformations like multiplication.

**Example:**

A source record in a CSV file might have a number like `7.00E-05` that appears as a string in the field named `source_field`.

```json JSON theme={null}
 { "column_1": "to_number(source_field)" }
```

This JSON code converts the string in exponential notation into the double `0.00007` that is now suitable for loading into decimal, double, or float columns, or for use in mathematical transformation functions.

### Flatten, Array Access, and Pipes

For general syntax help for arrays, projections, and pipe expressions, see [JMESPath website](https://jmespath.org/specification.html). Flattening arrays, accessing entire arrays, and using pipe expressions are three important operations when you work with nested data in LAT transformations.

* The flatten operator (e.g., `[]`) flattens an array one level. This also removes NULL values from the array.

<Warning>
  This can cause unintended consequences if multiple arrays need to remain aligned for data processing.
</Warning>

* When you work with nested arrays, you can maintain array hierarchy. For example, you can replace an array index such as `*` with `[*]` to maintain the hierarchy.
* You can use the pipe operator (e.g., `a.b[*] | [0]`) to stop the projection of an expression into the prior expression.

## Related Links

[Ingest Data with Legacy LAT Reference](/ingest-data-with-legacy-lat-reference)