Overview
It is possible to use SQL to extract data from the nested source dataset (such as JSON or XML) and convert it to a flat or nested array.
Applying an SQL transformation to the nested dataset will create an array of columns and rows which can be mapped, for example, to the table in the relational database.
Write queries for nested data objects.
Let's assume that the JSON document below is returned by the web service.
{
"requestId":"185d6#14b51985ff0",
"success":true,
"result":[
{
"id":"433455454",
"name":"all fields",
"searchableFields":[
{
"name":"field1",
"displayName":"Just field 1",
"dataType":"string",
"length":36,
"updateable":false
},
{
"name":"field2",
"displayName":"Just field 2",
"dataType":"string",
"length":50,
"updateable":false
},
],
"fields":[
{
"name":"leadId",
"displayName":"Lead Id",
"dataType":"integer",
"updateable":false
},
{
"name":"role",
"displayName":"Role",
"dataType":"string",
"length":50,
"updateable":false
},
{
"name":"isPrimary",
"displayName":"Is Primary",
"dataType":"boolean",
"updateable":true
},
{
"name":"externalCreatedDate",
"displayName":"External Created Date",
"dataType":"datetime",
"updateable":true
}
]
}
]
}
The goal is to extract all searchableFieldsand fieldsfrom the nested JSON and create a flat CSV file.
This can be accomplished by using the following SQL query, which is applied to the nested source JSON:
select name, displayName, 'searchable' as fieldType from result.searchableFields union select name, displayName, 'regular' as fieldType from result.fields
The idea is to provide a path to the nested dimension in the FROM clause. The elements of the path are separated by a comma (,) or dot (,). Using the example above, the path to the nested attributes under the searhableFields is result.searhableFieldsand the path to the nested attributes under the fields is result.fields.
If you want to include some of the fields from theparentobject, as well as fields from thenested object, use theowner.qualifier:
select result.name as apiName, searchableFields.name, searchableFields.displayName,
'searchable' as fieldType
from result.searchableFields
union
select result.name as apiName, fields.name, fields.displayName,
'regular' as fieldType
from result.fieldsRead more about writing SQL queries for extracting data from nested data objects.
Process
Step 1. Create an ETL flow where the source is a file.
Step 2. Enter Source query.
Step 3. Optionally add a mapping.
Testing SQL queries in Explorer
Step 1. Select Explorer
Step 2. Expand nested data object
Step 3. Click Develop SQL
Step 4. Enter SQL in the editor
Step 5. Click Execute SQL.
Comments
0 comments
Please sign in to leave a comment.