ODBC JSON Driver for REST API and files

⚡ Key Benefits

• SQL-First API Access — Query REST APIs and JSON files with standard SQL syntax
• Nested JSON Flattening — Transform hierarchical payloads into tabular analytics-ready results
• Flexible Authentication — Connect using OAuth, JWT, X509, dynamic token, and basic auth modes
• Advanced API Controls — Configure pagination, headers, body, URL params, and joins in SQL
• Centralized Team Access — Share secure driver connectivity through Data Gateway without local installs

📸 Visual Overview & Setup

1. Configure JSON/REST Source 🔍 View Full Size	2. Configure OAuth/Auth 🔍 View Full Size	3. Write Query and Preview 🔍 View Full Size
4. Load Data in Power BI 🔍 View Full Size	5. Query from SQL Server 🔍 View Full Size	6. Centralize via Data Gateway Data Gateway overview

Seamless SQL Query Integration

SELECT *
FROM $
WITH (
  SRC = 'http://yourhost/api/some-service',
  DataConnectionType = 'OAuth',
  OAuthVersion = 'OAuth2',
  AuthUrl = 'https://yourhost/api/authorize',
  TokenUrl = 'https://yourhost/api/token',
  ClientId = 'xxxxxxxxxxx',
  ClientSecret = 'xxxxxxxxxxxxx',
  AccessToken = 'xxxxxxxxxxx',
  RefreshToken = 'xxxxxxxxxxx'
)

🔧 Key Features

Read and Transform JSON Data

• Read from web APIs, cloud endpoints, or local JSON files with SQL
• Flatten nested arrays and objects into relational columns
• Use WHERE, ORDER BY, GROUP BY, and custom math/string/datetime/JSON functions
• Handle wildcard file patterns and large compressed files

API Execution and Authentication

• Send GET/POST requests with custom headers, body, query parameters, and cookies
• Support pagination by URL path, URL parameter, status, or record-count strategies
• Use OAuth, JWT, X509, basic auth, NTLM, and dynamic token workflows
• Run multi-step API join patterns for parent-child extraction

Tool Integration and Deployment

• Works with Power BI, Tableau, SSRS, Excel, SQL Server, SSIS, and code apps
• Use Data Gateway for centralized managed access and secure sharing
• Reuse one driver setup across analytics, ETL, reporting, and operational workflows

💡 Common Use Cases

Typical scenarios for JSON REST API ODBC Driver in reporting, ETL, and automation:

• Power BI and Tableau API reporting: Query JSON endpoints directly in BI tools.
  Example: Build dashboards from SaaS API data without custom connectors.
• SQL Server API access: Query external REST data using linked server patterns via gateway.
  Example: Use OPENQUERY to join API data with warehouse tables.
• Automated ETL ingestion: Pull paged API data and flatten nested structures for staging.
  Example: Schedule recurring loads from cloud systems into relational targets.
• Secure token-based integration: Configure OAuth or dynamic token authentication in SQL workflows.
  Example: Refresh protected API sessions without writing custom auth code.
• Bulk API and file operations: Upload content, call write endpoints, and process large JSON files.
  Example: Post updates to APIs and validate responses in one query step.

🎯 Summary

JSON REST API ODBC Driver turns API access into a SQL-native workflow for data teams.
It combines flexible authentication, nested JSON transformation, and centralized deployment for scalable BI and ETL integration.

Trusted by Developers & IT Teams Worldwide Built for ODBC Workloads: One driver model across BI, ETL, databases, and custom code. Expert Technical Support: Engineers help with connection setup, SQL patterns, and performance tuning. Proven Enterprise Scale: Thousands of teams rely on ZappySys for API and cloud connectivity.

ODBC PowerPack Includes JSON REST API ODBC Driver Read reviews on Capterra →