JsonifySQL 0.8.0

JsonifySQL

JsonifySQL is a .NET library that allows querying data through a structured JSON request format. It provides flexible filtering, ordering, pagination, and schema discovery capabilities, making it easy to build dynamic, data-driven APIs.

📦 Installation

Install via NuGet Package Manager:

Install-Package JsonifySQL

Or via .NET CLI:

dotnet add package JsonifySQL

✨ Features

• 🔍 Query data using a powerful and intuitive JSON format
• 🧾 Discover available schemas via schema requests
• 🔗 Join related data with configurable relationships
• 📄 Apply dynamic filters and ordering
• 📊 Use aggregation functions (sum, avg, min, max)
• 📃 Support for both offset and cursor-based pagination
• 👥 Profile-based access control to queryable objects
• Currently only supporting postgresql parser

⚙️ Configuration

Define searchable objects using a jsonifysql.conf file:

{
  "clients": {
    "alias": "c",
    "visibleOnSchemaQuery": true,
    "cursorField": {
      "name": "cid",
      "type": "integer"
    },
    "fields": {
      "cid": { "type": "integer", "description": "Client identifier" },
      "name": { "type": "string", "description": "Name of the client" }
    },
    "relations": {
      "docs": { "joinField": "cid", "onField": "cid" }
    },
    "fixedOrderBy": {
      "cid": "ASC"
    },
    "access": ["Admin", "DefaultUser"]
  }
}

Optional elements: relations, fixedConditions, fixedMerges, fixedOrderBy, and forProfile. Notes: build the jsonifysql.conf file as content.

🚀 Usage

1. Register Services

In your Startup.cs or Program.cs:

builder.Services.AddJsonifySQL("jsonifysql.conf");
builder.Services.AddScoped<IJsonifySQLParser, PostgresJsonifySQL>(); // For PostgreSQL

2. Inject and Use the Parser

public class MySearch
{
    private readonly IJsonifySQLParser _jsonifyParser;

    public MySearch(IJsonifySQLParser jsonifySQLParser)
    {
        _jsonifyParser = jsonifySQLParser;
    }

    public async Task<object> DoSearch(string jsonQuery)
    {
		object? finalResult = null;
	
        var parserResult = _jsonifyParser.ParseJsonQuery(jsonQuery, profile: userProfile, parameterValues: parameters);

        if (!parserResult.Success)
            return parserResult.ParseStatusMessage;

        if (parserResult.IsDataQuery)
        {
            // Execute raw SQL: parserResult.FinalQuery & parserResult.PaginationQuery
			// if using dapper, you can use the query multiple function and execute the two statements
        }

        if (parserResult.IsSchemaQuery)
        {
            finalResult = JsonSerializer.Deserialize<object>(parserResult.SchemaResponse, new JsonSerializerOptions
            {
                PropertyNamingPolicy = JsonNamingPolicy.CamelCase,
                WriteIndented = true
            });
        }
		
		return finalResult;
    }
}

📘 Example JSON Requests

Simple Query

{
  "query": {
    "clients": {
      "select": ["cid", "name"]
    }
  }
}

With Filter

{
  "query": {
    "clients": {
      "select": ["cid", "name"],
      "where": {
        "name": { "ct": "John" }
      }
    }
  }
}

With Pagination

Cursor-based:

{
  "query": {
    "clients": {
      "select": ["cid", "name"]
    },
    "page": {
      "size": 100,
      "after": "cursorvalue"
    }
  }
}

Offset-based:

{
  "query": {
    "clients": {
      "select": ["cid", "name"]
    },
    "page": {
      "size": 100,
      "current": "1"
    }
  }
}

With Ordering

{
  "query": {
    "clients": {
      "select": ["cid", "name"],
      "orderBy": {
        "name": "ASC"
      }
    }
  }
}

Schema Request

{
  "schema": {}
}

🔎 Supported Operators

Comparison Operators

Logical Operators

• and
• or

Aggregation Functions

• sum
• min
• max
• avg
• count

📄 License

This project is licensed under the MIT License.