json2sql

Pure-Ruby SQL builder. Translates Ruby Hashes (or parsed JSON) into MySQL/MariaDB query strings.

No runtime dependencies
String and Symbol keys are both accepted
Target: MySQL 8.0+

Installation

gem install json2sql

Or in your Gemfile:

gem "json2sql"

Usage

require "json2sql"

All entry points are stateless class methods that return a SQL string.

SELECT

Json2sql::SelectRunner.build(hash) → String

The result is always a SELECT JSON_OBJECT(…) query. Multiple top-level keys produce multiple named subqueries wrapped in a single outer JSON_OBJECT.

Basic

Json2sql::SelectRunner.build(
  "users" => { "columns" => ["id", "name", "email"] }
)

WHERE conditions

Conditions live under the "and" or "or" key.

Json2sql::SelectRunner.build(
  "users" => {
    "columns" => ["id", "name"],
    "and" => {
      "active" => 1,       # Integer → col = 1
      "name"   => "john",  # String  → col LIKE '%john%'
    }
  }
)

Explicit operators

Key	SQL
`{ "=" => value }`	`col = value`
`{ "!=" => value }` or `{ "<>" => value }`	`col != value`
`{ ">" => value }`	`col > value`
`{ ">=" => value }`	`col >= value`
`{ "<" => value }`	`col < value`
`{ "<=" => value }`	`col <= value`
`{ "in" => [1, 2, 3] }`	`col IN (1, 2, 3)`
`{ "!in" => [1, 2] }`	`col NOT IN (1, 2)`
`{ "like" => "%.com" }`	`col LIKE '%.com'`
`{ "!like" => "%.com" }`	`col NOT LIKE '%.com'`
`{ "contains" => "john" }`	`col LIKE '%john%'`
`{ "first" => "Jo" }`	`col LIKE 'Jo%'`
`{ "last" => "son" }`	`col LIKE '%son'`
`{ "null" => true }`	`col IS NULL`
`{ "null" => false }`	`col IS NOT NULL`

Json2sql::SelectRunner.build(
  "users" => {
    "columns" => ["id", "name"],
    "and" => {
      "age"        => { ">=" => 18 },
      "role"       => { "!in" => [0, 9] },
      "deleted_at" => { "null" => true },
      "email"      => { "last" => ".com" }
    }
  }
)

Column cross-references

Use "$.table.column" syntax to reference another column instead of a literal value:

"and" => { "author_id" => { "=" => "$.users.id" } }
# → `posts`.`author_id` = `users`.`id`

Nested AND / OR

"and" => {
  "active" => 1,
  "or" => { "role" => 1, "admin" => 1 }
}

ORDER BY

"order" => { "created_at" => "desc", "name" => "asc" }

LIMIT and OFFSET

"limit" => 20, "offset" => 40

Total count (`options`)

Adding "options" => ["total"] wraps the result in { "data": […], "total": N } by running an additional COUNT(*) subquery.

Json2sql::SelectRunner.build(
  "users" => {
    "columns" => ["id", "name"],
    "and"     => { "active" => 1 },
    "order"   => { "created_at" => "desc" },
    "limit"   => 20,
    "offset"  => 0,
    "options" => ["total"]
  }
)

Function columns and bigint-safe JSON

Function columns in SELECT use objects in the columns array with alias, function and params.

You can use generic SQL CAST in function columns via:

{"alias" => "field", "function" => "CAST", "params" => ["source_column", "TYPE"]}

To return 64-bit integer columns as JSON strings (avoiding precision loss in JS clients), use TYPE = "CHAR":

Json2sql::SelectRunner.build(
  "devices" => {
    "columns" => [
      { "alias" => "id", "function" => "CAST", "params" => ["id", "CHAR"] },
      "name"
    ]
  }
)

This generates a JSON value like CAST(devices.id AS CHAR) for the aliased key.

Nested children (one-to-many)

Json2sql::SelectRunner.build(
  "users" => {
    "columns"  => ["id", "name"],
    "children" => {
      "posts" => { "columns" => ["id", "title"] }
    }
  }
)
# JOIN condition: `posts`.`user_id` = `users`.`id`

Nested parents (many-to-one)

Json2sql::SelectRunner.build(
  "posts" => {
    "columns" => ["id", "title"],
    "parents" => {
      "users" => { "columns" => ["id", "name"] }
    }
  }
)
# JOIN condition: `posts`.`user_id` = `users`.`id`

Nesting is recursive — children can have their own children.

Multiple tables

Json2sql::SelectRunner.build(
  "users"    => { "columns" => ["id", "name"] },
  "products" => { "columns" => ["id", "price"] }
)
# → SELECT JSON_OBJECT('users', (…), 'products', (…));

INSERT

Json2sql::InsertRunner.build(hash) → String

"columns" is a Hash of column => value.

Single row

Json2sql::InsertRunner.build(
  "users" => { "columns" => { "name" => "João", "email" => "joao@example.com" } }
)
# → INSERT INTO `users` (`name`, `email`) VALUES ('João', 'joao@example.com');

Multiple rows

Pass an Array of row hashes:

Json2sql::InsertRunner.build(
  "tags" => [
    { "columns" => { "name" => "ruby" } },
    { "columns" => { "name" => "rails" } }
  ]
)
# → INSERT INTO `tags` (`name`) VALUES ('ruby');
#   INSERT INTO `tags` (`name`) VALUES ('rails');

UPDATE

Json2sql::UpdateRunner.build(hash) → String

Json2sql::UpdateRunner.build(
  "users" => {
    "columns" => { "name" => "Maria", "active" => 1 },
    "and"     => { "id" => 42 }
  }
)
# → UPDATE `users` SET `users`.`name` = 'Maria', `users`.`active` = 1 WHERE (`users`.`id` = 42);

DELETE

Json2sql::DeleteRunner.build(hash) → String

Json2sql::DeleteRunner.build(
  "users" => { "and" => { "id" => 42 } }
)
# → DELETE FROM `users` WHERE (`users`.`id` = 42);

Value types

Ruby type	SQL output
`Integer`	raw number
`Float`	raw number
`String`	`'escaped value'`

Single quotes in strings are doubled (O'Brien → 'O''Brien'). Backslashes are escaped.

Security

Table and column names are sanitized by stripping characters outside [a-zA-Z0-9_-]. Malformed identifiers become mangled but harmless (e.g. "users; DROP TABLE" → `usersDROPTABLE`). Values are always wrapped in quoted literals.

QueryPolicy

Json2sql::QueryPolicy sanitizes a raw input Hash before passing it to any Runner. Use it as a server-side access control layer — it enforces which tables, columns, children and parents a caller may query, and injects forced WHERE conditions to prevent IDOR.

policy = Json2sql::QueryPolicy.new(
  mode:   :allow,
  tables: {
    orders: {
      columns:  %w[id total status],
      children: { order_items: { columns: %w[id price] } },
      parents:  { users:       { columns: %w[id name] } },
      where:    { "and" => { "user_id" => 42 } }
    }
  }
)

safe_input = policy.apply(raw_params)
sql        = Json2sql::SelectRunner.build(safe_input)

mode

Value	Behaviour
`:allow` (default)	Only tables listed in `tables:` are accessible. Tables absent from the config are blocked entirely. Empty `tables:` blocks everything.
`:deny`	All tables pass. After column filtering, tables with no remaining columns are removed. Same rule applies to children and parents.

Per-table configuration

{
  table_name => {
    columns:  [...],          # column list
    children: { child  => { columns: [...], ... } },
    parents:  { parent => { columns: [...], ... } },
    where:    { "and"  => { col => val } }
  }
}

Key	`:allow` mode	`:deny` mode
`columns` absent or nil	all columns blocked	columns untouched
`columns: [...]`	only listed columns pass; function-column Hashes always pass	listed columns are removed
`children` / `parents` absent	all relations blocked	relations untouched
`children: { t => {...} }`	only listed child tables pass	listed child tables are removed
`where: { "and" => {...} }`	forced conditions merged into `"and"`; forced keys overwrite user-supplied values	same

IDOR guard

Forced where keys always overwrite the user-supplied value for the same column:

# config: where: { "and" => { "user_id" => 42 } }
# user sends: "and" => { "user_id" => 999 }
# result:     "and" => { "user_id" => 42 }   ← attacker cannot override

Recursive nesting

children and parents configs are recursive — each nested table accepts its own columns, children, parents and where.

Json2sql::QueryPolicy.new(
  tables: {
    orders: {
      columns: %w[id total],
      children: {
        order_items: {
          columns: %w[id price],
          parents: { products: { columns: %w[id name] } }
        }
      }
    }
  }
)

String and Symbol keys

Both are accepted — normalized internally.

Json2sql::QueryPolicy.new(tables: { orders: { columns: %w[id] } })
# same as
Json2sql::QueryPolicy.new(tables: { "orders" => { "columns" => %w[id] } })

Pitfalls

No boolean equality — use 1/0. true/false only works with the "null" operator.
"options" => ["total"] doubles query cost — runs two subqueries. Ensure proper indexes.
Empty in array emits IN (NULL) — always false, intentional.
LATERAL subqueries — requires MySQL 8.0+.

Development

bundle exec rake test     # run test suite
bundle exec rake build    # build .gem to pkg/
bundle exec rake release  # tag + push + publish to RubyGems.org

License

MIT

json2sql

Development

json2sql

Installation

Usage

SELECT

Basic

WHERE conditions

Explicit operators

Column cross-references

Nested AND / OR

ORDER BY

LIMIT and OFFSET

Total count (options)

Function columns and bigint-safe JSON

Nested children (one-to-many)

Nested parents (many-to-one)

Multiple tables

INSERT

Single row

Multiple rows

UPDATE

DELETE

Value types

Security

QueryPolicy

mode

Per-table configuration

IDOR guard

Recursive nesting

String and Symbol keys

Pitfalls

Development

License

Total count (`options`)