json_sql.rb

# We've been storing some JSON in a string field in our database,
# and needed to extract it to get something resembling reasonable
# performance on queries.
# 
# Building a manual query of either INSERT INTO () SELECT or
# UPDATE forms, use it like:
# 
#   SELECT #{json('login')}
# 
# If you have integer fields that are sometimes null or not present:
# 
#   SELECT CAST (CONCAT('0',#{json('count')}) AS UNSIGNED)
# 
# and it will replace null values with zero.

  def json(key)
    key_json = "\"#{key}\":"

    # key start/end locations, including ""
    k_a = "LOCATE('#{key_json}', params)"
    k_z = "LOCATE('\"', params, #{k_a}+1)" # this is terminating "

    # is there a space after colons?
    spad = "IF(LOCATE('\": ', params), 1, 0)"

    # is value a string?
    val_string = "LOCATE(CONCAT('#{key_json}', IF(#{spad},' ',''), '\"'), params, #{k_a})"
    qpad = "IF(#{val_string}, 1, 0)"

    # value start/end locations, excluding "" if present
    v_a = "(#{k_z}+1 + 1 + #{spad} + #{qpad})" # 1 for colon, spad for optional space, qpad for possible quote

    end_if_string = "LOCATE('\"', params, #{v_a})"
    end_if_not_string = "IF(LOCATE(',', params, #{v_a}), LOCATE(',', params, #{v_a}), LOCATE('}', params, #{v_a}))"

    v_z = "IF(#{val_string}, #{end_if_string}, #{end_if_not_string})"

    value_string = "SUBSTRING(params FROM #{v_a} FOR (#{v_z} - #{v_a}))"
    "IF(#{k_a}, #{value_string}, '')"
  end