PostgreSqlJsonbSetDeep.kt

/**
 * Helper function that builds a PostgreSQL jsonb_set function that also deeply creates missing objects
 *
 * Usage:
 *  1. jsonbSetDeep(target = "my_field", path = arrayOf("depth1","depth2"), newJsonValue = "[]")
 *     returns -
 *      jsonb_set(
 *       CASE
 *          WHEN  my_field IS NULL THEN '{"depth1": {}}'
 *          WHEN  my_field -> 'depth1' IS NULL THEN jsonb_set(my_field,  array['depth1'], '{}')
 *          ELSE  my_field
 *       END,
 *       array['depth1','depth2'],
 *       '[]'
 *      )
 *
 * @param target - valid json element
 * @param path - array of non-empty valid json properties that describes the search path
 * @param newJsonValue - valid json element
 */
fun jsonbSetDeep(target: String, path: Array<String>, newJsonValue: String): String {
  return StringBuilder().apply {
    appendLine("jsonb_set(")

    if (path.isEmpty()) {
      appendLine("$target,")
    } else {
      appendLine(" CASE")
      appendLine("    WHEN  $target IS NULL THEN '${buildJsonStructure(jsonPath = path, to = path.size - 1)}' ")

      for (i in 0 until path.size - 1) {
        val jsonPointer = buildJsonExtractFieldOperator(target, path, i + 1)
        val jsonPath = buildJsonPath(path, i + 1)
        val newValue = buildJsonStructure(path, from = i + 1, to = path.size - 1)
        appendLine("    WHEN $jsonPointer IS NULL THEN jsonb_set($target, $jsonPath, '$newValue') ")
      }

      appendLine("    ELSE  $target ")
      appendLine(" END,")
    }

    appendLine("${buildJsonPath(path, path.size)},")
    appendLine(" '$newJsonValue'")
    appendLine(")")
  }.toString()
}

private fun buildJsonStructure(jsonPath: Array<String>, from: Int = 0, to: Int = jsonPath.size): String {
  return StringBuilder().apply {
    for (i in from until to) {
      append("""{"${jsonPath[i]}": """)
    }

    append("{}")
    append("}".repeat(to - from))
  }.toString()
}

private fun buildJsonPath(jsonPath: Array<String>, to: Int = 0): String {
  return StringBuilder(" array[").apply {
    for (i in 0 until to) {
      append("'${jsonPath[i]}'")

      if (i < to - 1) {
        append(",")
      }
    }

    append("]")
  }.toString()
}

private fun buildJsonExtractFieldOperator(field: String, jsonPath: Array<String>, to: Int = 0): String {
  return StringBuilder(""" $field -> """).apply {
    for (i in 0 until to) {
      append("'${jsonPath[i]}'")

      if (i < to - 1) {
        append("->")
      }
    }
  }.toString()
}