Feedboards.Json.Sqlify 1.0.0

Feedboards.Json.Sqlify

A .NET library for converting JSON data structures into SQL schemas and tables. Currently supports ClickHouse with planned support for MSSQL, PostgreSQL, MySQL, and more. This library helps you automatically generate SQL table definitions from JSON data, handling nested structures and complex data types.

Features

• Automatic SQL schema generation from JSON files
• Support for nested JSON structures
• Handles both single files and directories of JSON files
• Configurable maximum depth for nested structures
• Automatic type detection and mapping to database-specific data types
• Support for ClickHouse Nested type structures
• Planned support for multiple databases:
  • ClickHouse (current)
  • MSSQL (planned)
  • PostgreSQL (planned)
  • MySQL (planned)

Installation

Building from Source

# Clone the repository
git clone https://github.com/yourusername/Feedboards.Json.Sqlify.git
cd Feedboards.Json.Sqlify

# Build the solution
dotnet build

NuGet Package (Coming Soon)

dotnet add package Feedboards.Json.Sqlify

Quick Start

// Initialize the client with options
var options = new ClickHouseOption
{
    PathToFolderWithJson = "path/to/json/files",
    PathToOutputFolder = "path/to/output"
};

var client = new ClickHouseClient(options);

// Generate SQL from a single JSON file
client.GenerateSQL(
    jsonFolder: "path/to/input.json",
    outputFolder: "path/to/output.sql",
    tableName: "my_table",
    maxDepth: 10  // Optional, defaults to 10
);

// Or process an entire directory of JSON files
client.GenerateSQL(
    jsonFolder: "path/to/json/folder",
    outputFolder: "path/to/sql/folder"
);
// Note: When processing a directory, table names will be derived from JSON filenames.
// Avoid using special characters (like dots) in filenames.

JSON to SQL Mapping Example

Input JSON:

{
  "test": [
    {
      "name": "example",
      "title": "test1"
    }
  ]
}

Generated SQL:

CREATE TABLE IF NOT EXISTS test_table (
    `test` Nested(
        `name` String,
        `title` String
    )
) ENGINE = MergeTree()
ORDER BY tuple();

Nested Structures and flatten_nested Setting

When dealing with nested structures inside other nested structures, the library automatically adds the flatten_nested=0 setting to ensure proper handling of complex nested data. For example:

Input JSON with nested-in-nested structure:

{
  "products": [
    {
      "name": "Product 1",
      "variants": [
        {
          "size": "M",
          "colors": [
            {
              "name": "Red",
              "code": "#FF0000"
            }
          ]
        }
      ]
    }
  ]
}

Generated SQL:

SET flatten_nested=0;

CREATE TABLE IF NOT EXISTS products_table (
    `products` Nested(
        `name` String,
        `variants` Nested(
            `size` String,
            `colors` Nested(
                `name` String,
                `code` String
            )
        )
    )
) ENGINE = MergeTree()
ORDER BY tuple();

The flatten_nested=0 setting is automatically added when:

• A nested structure contains another nested structure
• The nesting depth is greater than 1 level
• Complex nested arrays or objects are present

This setting ensures that ClickHouse preserves the hierarchical structure of your data and allows for proper querying of nested fields.

API Reference

ClickHouseClient

The main class for generating SQL schemas from JSON.

Note: Some methods shown in the API Reference are currently in development and may not work as expected. Only the methods demonstrated in the Quick Start section are fully implemented and tested.

Constructor

public ClickHouseClient(ClickHouseOption? option = null)

Methods

GenerateSQL Overloads

1. Using configuration options:

public bool GenerateSQL(string tableName, int? maxDepth = 10)

2. Using specific folder type:

public bool GenerateSQL(string folderPath, FolderType folderType, string? tableName = null, int? maxDepth = 10)

3. Using explicit paths:

public bool GenerateSQL(string jsonFolder, string outputFolder, string? tableName = null, int? maxDepth = 10)

Configuration

public class ClickHouseOption
{
    public string? PathToFolderWithJson { get; set; }
    public string? PathToOutputFolder { get; set; }
}

Data Type Mapping

Best Practices

1. Always specify a table name when processing single files
2. Keep nested structures within reasonable depth (recommended max: 10)
3. Ensure consistent JSON structure across files when processing directories
4. Use appropriate file permissions for input/output directories
5. Avoid using special characters in filenames when processing directories
6. Consider the performance impact of deeply nested structures

Error Handling

The library provides error handling for common scenarios:

• Invalid file paths
• Mismatched folder/file combinations
• Invalid JSON structures
• Excessive nesting depth
• Missing required parameters
• File permission issues
• Invalid table names

Contributing

Contributions are welcome! Please feel free to submit a Pull Request. Before submitting:

1. Fork the repository
2. Create your feature branch
3. Commit your changes
4. Push to the branch
5. Create a new Pull Request

License

This project is licensed under the MIT License - see the LICENSE file for details.

Support

For support, please:

1. Check the documentation
2. Open an issue in the GitHub repository
3. Contact the maintainers

Roadmap

• Add an advanced error system
• Add support for MSSQL
• Add support for PostgreSQL
• Add support for MySQL
• Improve type inference
• Add support for custom type mappings
• Add support for schema validation