LAT Data Transformation with JMESPath

data pipelines are now the preferred method for loading data into the ocient system for details, see docid\ xq0tg7yph vn62uwufibu 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 docid\ ogtlpyxjm1lxxexmufuxl section for a list of functions to modify data during loading, see the docid\ ogtlpyxjm1lxxexmufuxl page information on customizing your own transformation functions can be found on the docid 2fwibydfm60beculawwtc page for expressions to select elements from an array matching certain criteria, see the docid 07kl0zrg6cqqarqhmmmif 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 { "column 1" "`0`", "column 2" "`1`", "column 3" "`true`", "column 4" "`\[]`" } wrong { "column 1" "0", "column 2" "1", "column 3" "true", "column 4" "\[]" } you can write string literals with a single quote { "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 { "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 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 this can cause unintended consequences if multiple arrays need to remain aligned for data processing 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 docid\ tt6tfoulap0mt aycm2ka