Generate Nested JSON Using SQL and CLOB Format

Overview

This technique enables you to generate complex, nested JSON documents from flat data sources such as relational database tables or Connected Apps (e.g., Salesforce) using SQL over staging tables and serialize the result directly as JSON using the CLOB format.

It combines the flexibility of flat-to-flat ETL with the power of SQL-based JSON generation available in SQLite, allowing full control over the document structure while preserving the performance and scalability of a structured ETL pipeline.

When to Use

This approach is ideal when:

• Your source data is relational/flat but the output must be deeply nested

• You need to build JSON payloads for API uploads

• You want complete control over the structure of the output document

• You want to work with dynamic JSON without scripting

How It Works

Step 1: Extract Flat Data into SQLite

Use an ETL flow to extract records from flat data sources—such as database tables or objects in connected apps (like Salesforce, HubSpot, or NetSuite)—into staging tables in SQLite.

These can be either in-memory or persisted and serve as the raw input for document generation.

Step 2: Generate Nested JSON Using SQL

Use SQLite’s built-in JSON functions like json_object and json_group_array to construct a hierarchical JSON structure. This allows full control over:

• Object and array nesting

• Conditional structures

• Field transformation and grouping

• Joining multiple sources

Step 3: Serialize JSON Using CLOB Format

The result of the SQL query is a single JSON document stored in a CLOB column. Use the CLOB format as the destination format in your transformation to serialize and write the output as-is, preserving the JSON structure.

Real-Life Example: Salesforce → Nested JSON Document

Use Case

You need to export a list of customers from Salesforce where each customer includes:

• Basic company details from the Account object

• Associated contacts from the Contact object

The output should be a structured JSON document suitable for downstream consumption (e.g., analytics, APIs, archiving).

Step-by-Step Setup

Step 1: Extract Account and Contact into SQLite.

Create SQLite connection for staging data.

Create an any-to-any ETL flow with two transformations:

• Salesforce → SQLite for Account

• Salesforce → SQLite for Contact

Step 2: Create a Transformation from SQLite to File

Add a third transformation to the flow that reads from the account staging table and writes the output to a .json file using the CLOB format.

Step 3: Use SQL to Generate the Document

In the Source Query, enter a SQL statement like the one below to generate the nested document:

SELECT json_object(
'Producer', 'RandomProducer',
'ProducerKey', 'RandomKey',
'ProducerKeyId', 'RandomKeyId',
'IntegrationRunId', 'RandomIntegrationRunId',
'InterfaceRunId_SubNr', 'RandomInterfaceRunId',
'Customers', json_group_array(
json_object(
'Customer_References', json_object(
'ERP', json_object(
'Customer_Number', acc.AccountNumber,
'Customer_Long_Number', acc.AccountNumber,
'Global_Location_Number', acc.DunsNumber
)
),
'General_Information', json_object(
'Customer_Name', json_object(
'Alpha', acc.Name,
'Mailing', acc.Name
),
'Customer_Classification', json_object(
'Industry_Classification_Code', acc.NaicsCode,
'Industry_Classification_Description', acc.NaicsDesc
)
),
'Address_Information', json_object(
'Address_Line_1', acc.BillingStreet,
'Postal_Code', acc.BillingPostalCode,
'City', acc.BillingCity,
'State_Code', acc.BillingState,
'Country_Code', acc.BillingCountry
),
'Contact_Information', (
SELECT json_group_array(
json_object(
'Phone_Number', c.Phone,
'Fax_Number', c.Fax,
'Mobile_Number', c.MobilePhone,
'Email_Address', c.Email,
'Website_URL', acc.Website
)
)
FROM contact c
WHERE c.AccountId = acc.Id
)
)
)
) AS result
FROM account acc;

Result

This query produces a JSON document like:

{
"Producer": "RandomProducer",
"ProducerKey": "RandomKey",
"ProducerKeyId": "RandomKeyId",
"IntegrationRunId": "RandomIntegrationRunId",
"InterfaceRunId_SubNr": "RandomInterfaceRunId",
"Customers": [
{
"Customer_References": {
"ERP": {
"Customer_Number": "CC213425",
"Customer_Long_Number": "CC213425",
"Global_Location_Number": null
}
},
"General_Information": {
"Customer_Name": {
"Alpha": "Pyramid Construction Inc.",
"Mailing": "Pyramid Construction Inc."
},
"Customer_Classification": {
"Industry_Classification_Code": null,
"Industry_Classification_Description": null
}
},
"Address_Information": {
"Address_Line_1": "2 Place Jussieu",
"Postal_Code": "75251",
"City": "Paris",
"State_Code": null,
"Country_Code": "France"
},
"Contact_Information": [
{
"Phone_Number": "(014) 427-4427",
"Fax_Number": "(014) 427-4428",
"Mobile_Number": "(014) 454-6364",
"Email_Address": "pat@pyramid.net",
"Website_URL": "www.pyramid.com"
}
]
}
]
}