Module: Profitable::JsonHelpers

Included in:
Profitable
Defined in:
lib/profitable/json_helpers.rb

Constant Summary collapse

VALID_TABLE_COLUMN_PATTERN =

Regex patterns for validating SQL identifiers to prevent SQL injection Only allows: alphanumeric characters, underscores, and dots (for table.column format)

/\A[a-zA-Z_][a-zA-Z0-9_.]*\z/
VALID_JSON_KEY_PATTERN = 
/\A[a-zA-Z_][a-zA-Z0-9_]*\z/

Instance Method Summary collapse

Instance Method Details

#json_extract(table_column, json_key) ⇒ String

Returns the appropriate JSON extraction syntax for the current database adapter, always yielding a TEXT-typed value so comparisons behave identically everywhere. Supports PostgreSQL, MySQL (5.7.9+), and SQLite.

Examples:

PostgreSQL

json_extract('pay_charges.object', 'paid')
# => "pay_charges.object ->> 'paid'"

MySQL

json_extract('pay_charges.object', 'paid')
# => "JSON_UNQUOTE(JSON_EXTRACT(pay_charges.object, '$.paid'))"

SQLite

json_extract('pay_charges.object', 'paid')
# => "CAST(json_extract(pay_charges.object, '$.paid') AS TEXT)"

Parameters:

  • table_column (String) —

    The table and column name (e.g., ‘pay_charges.object’)

  • json_key (String) —

    The JSON key to extract (e.g., ‘paid’, ‘status’)

Returns:

  • (String) —

    Database-specific SQL for JSON extraction

Raises:

  • (ArgumentError) —

    if table_column or json_key contain invalid characters



30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
 
# File 'lib/profitable/json_helpers.rb', line 30

def json_extract(table_column, json_key)
  # Validate inputs to prevent SQL injection
  validate_table_column!(table_column)
  validate_json_key!(json_key)

  adapter = ActiveRecord::Base.connection.adapter_name.downcase

  case adapter
  when /postgres/
    "#{table_column} ->> '#{json_key}'"
  when /mysql/, /trilogy/
    # MySQL 5.7.9+ supports JSON_EXTRACT and ->> operator
    # We use JSON_UNQUOTE(JSON_EXTRACT()) for maximum compatibility
    "JSON_UNQUOTE(JSON_EXTRACT(#{table_column}, '$.#{json_key}'))"
  when /sqlite/
    # SQLite returns JSON booleans as integers (0/1), unlike ->> on
    # PostgreSQL/MySQL which return text. CAST keeps the adapters in sync.
    "CAST(json_extract(#{table_column}, '$.#{json_key}') AS TEXT)"
  else
    # Fallback to PostgreSQL syntax for unknown adapters
    Rails.logger.warn("Unknown database adapter '#{adapter}' for JSON extraction. Falling back to PostgreSQL syntax.")
    "#{table_column} ->> '#{json_key}'"
  end
end