Skip to main content
Data mappings define transformation rules for extracting and normalizing JSON data from JSONB columns. This allows you to flatten nested JSON structures into separate columns or tables.

​
How Data Mappings Work

Data mappings use JSON paths to identify objects or arrays within JSONB columns. The path syntax uses JSONPath notation:
  • '$' refers to the root object
  • '$.metadata' refers to a property named metadata within the root
  • '$.tags.tagList' refers to a nested property tagList within tags
An object or array will only be transformed if all of its parent paths are also mapped. For example, a mapping for '$.a.b' requires mappings for both '$.a' and '$'.

​
Create Data Mapping

Define a new mapping for data transformation.

​
Syntax

create data mapping for mapping_type
    from table table_name column column_name path 'object_path'
    to 'target_identifier'

​
Parameters

​
mapping_type
string
required
The type of data mapping. Currently only json is supported.
​
table_name
string
required
The table to transform (schema-qualified name).
​
column_name
string
required
The column containing JSON data to transform.
​
object_path
string
required
Path to a JSON object or array using JSONPath syntax (e.g., '$', '$.metadata', '$.tags.tagList').
​
target_identifier
string
required
A short identifier to be used in naming the transformed data. Must be:
  • All lowercase
  • No more than 16 characters
  • Unique for the transformed column (no two paths can map to the same identifier)

​
Examples

​
Basic JSON Transformation

Transform a JSON column by mapping the root object: 
create data mapping for json
    from table library.inventory__ column jsondata path '$'
    to 't';

​
Nested Object Transformation

Extract metadata from a nested object: 
create data mapping for json
    from table library.inventory__ column jsondata path '$.metadata'
    to 'metadata';

​
Complete Hierarchical Mapping

To transform an array within nested objects, you must map all parent paths: 
-- Map the root object
create data mapping for json
    from table library.inventory__ column jsondata path '$'
    to 't';

-- Map the parent object
create data mapping for json
    from table library.inventory__ column jsondata path '$.metadata'
    to 'metadata';

-- Map another parent object
create data mapping for json
    from table library.inventory__ column jsondata path '$.tags'
    to 'tags';

-- Map the nested array
create data mapping for json
    from table library.inventory__ column jsondata path '$.tags.tagList'
    to 'taglist';

​
Visual Example

For the following JSON structure: 
{
  "id": "123",
  "metadata": {
    "createdDate": "2024-01-01",
    "updatedDate": "2024-01-15"
  },
  "tags": {
    "tagList": ["fiction", "bestseller"]
  }
}
The mappings above would create transformed columns or tables for each path, allowing you to query the nested data as relational structures.

​
Drop Data Mapping

Remove a data mapping configuration.

​
Syntax

drop data mapping for mapping_type
    from table table_name column column_name path 'object_path'

​
Parameters

​
mapping_type
string
required
The type of data mapping (e.g., json).
​
table_name
string
required
The table being transformed.
​
column_name
string
required
The column being transformed.
​
object_path
string
required
The JSON path to remove.

​
Example

Remove a data mapping: 
drop data mapping for json
    from table library.inventory__ column jsondata path '$.tags.tagList';

​
List Data Mappings

View all configured data mappings: 
list data_mappings;

​
Best Practices

Before creating mappings, plan the complete hierarchy of paths you need to extract. Remember that child paths require all parent paths to be mapped.
Choose target identifiers that clearly describe the data being extracted. This makes the resulting columns or tables easier to understand and query.
With a 16-character limit, use abbreviations when necessary but maintain clarity (e.g., 'metadata' instead of 'meta_information').

​
See Also

Data Sources

Configure external data sources

Alter Table

Modify table structures and data types

Build docs developers (and LLMs) love

Get started for freeTalk to us