How to pass multiple Query Parameter values to a GET web api?

We need more context here. Is this about one of these cases:

- a queryFilter for queryRecordType

- about a third party API that you want to pass values to

- or about an API that you create and you want to get values passed?

And try to explain this like to a three year old.

we have 2 Appian application and get value though web api call. 

One application require, all records from other application by passing multiple Ids as query parameter.

passing multiple value is not working, for single ids working fine.

yes, that's correct

?deliveryId=%7B3%2C2%7D

Do you think that I can help you without seeng what that query expression is doing !?!?

So you pass a string like "{1,2,3}" into a queryFilter using the "in" operator. What data type is that rule input?

I suggest to parse the string into a list of integers before passing it into the query.

a!localVariables(
  local!input: "{1,2,3}",
  tointeger(split(extract(local!input, "{", "}"), ","))
)

Thanks a Lot its working 

#DocumentIdsFileList

package com.surescripts.formularydownloadapi.documentIdsfilelist

import com.fasterxml.jackson.annotation.JsonProperty.Access
import com.fasterxml.jackson.annotation.{JsonCreator, JsonProperty}
import io.circe.derivation.{deriveDecoder, deriveEncoder}
import io.circe.{Decoder, Encoder}

@JsonCreator
case class DocumentIdsFileList(
                                @JsonProperty(value = "name", access = Access.READ_WRITE) name: String,
                                @JsonProperty(value = "pbm", access = Access.READ_WRITE) pbm: String,
                                @JsonProperty(value = "type", access = Access.READ_WRITE) `type`: String,
                                @JsonProperty(value = "subtype", access = Access.READ_WRITE) subtype: Option[String],
                                @JsonProperty(value = "size", access = Access.READ_WRITE) size: Int,
                                @JsonProperty(value = "created", access = Access.READ_WRITE) created: String,
                                @JsonProperty(value = "lastmodified", access = Access.READ_WRITE) lastModified: String,
                                @JsonProperty(value = "effective_date", access = Access.READ_WRITE) effectiveDate: String
                              )

object DocumentIdsFileList {
  implicit val decoder: Decoder[DocumentIdsFileList] = deriveDecoder[DocumentIdsFileList]
  implicit val encoder: Encoder[DocumentIdsFileList] = deriveEncoder[DocumentIdsFileList]
}



#DocumentIdsFileListRepository

package com.surescripts.formularydownloadapi.documentIdsfilelist

import com.surescripts.formularydownloadapi.Env
import com.surescripts.scala.cats.data.DataRepository
import doobie.implicits.toSqlInterpolator
import io.github.resilience4j.bulkhead.annotation.Bulkhead
import io.github.resilience4j.bulkhead.annotation.Bulkhead.Type
import io.github.resilience4j.circuitbreaker.annotation.CircuitBreaker
import io.github.resilience4j.ratelimiter.annotation.RateLimiter
import io.github.resilience4j.retry.annotation.Retry
import io.github.resilience4j.timelimiter.annotation.TimeLimiter
import org.springframework.stereotype.Repository

import java.time.LocalDate
import java.time.format.DateTimeFormatter
import java.util.concurrent.CompletableFuture
import scala.util.Try

@Repository
class DocumentIdsFileListRepository extends DataRepository {
  @Bulkhead(name = "default-bulkhead-db", `type` = Type.THREADPOOL)
  @TimeLimiter(name = "default-time-limiter-db")
  @CircuitBreaker(name = "default-circuit-breaker-db")
  @RateLimiter(name = "default-rate-limiter-db")
  @Retry(name = "default-retry-db")
  private val today: String = DateTimeFormatter.BASIC_ISO_DATE.format(LocalDate.now)

  def findDocumentIdsFileList(pbm: Option[String], `type`: Option[String], subtype: Option[String], createdAfter: Option[String], lastModifiedAfter: Option[String], drugDb: String, version: String)(implicit env: Env): CompletableFuture[Try[List[DocumentIdsFileList]]] = {
    val baseQuery = sql"""
      select fw.rollup_drug_db, fw.version, pmm.pid as pub_pid, pmm.PART_NM as PART_NM, fl.list_id, fl.type as list_type, fl.sub_type,
        fw.document_name, fw.document_id, fw.document_size, fw.creation_date, fw.last_modified_date, fw.rollup_drug_db_version, fl.effective_date
      from formulary_webdav fw
      inner join formulary_load fl on fw.formulary_load_id = fl.formulary_load_id and fl.management_status = 'A'
      inner join pmm_part pmm on fl.publisher = pmm.part_uid
      where exists(select * from pmm_service_contract sc
      inner join tp_service_direction sd on sc.service_id = sd.service_id and sd.direction = 'R'
      inner join tp_trx_in_service tis on sd.service_direction_id = tis.service_direction_id and tis.trx_type = 'FRMDST'
      where (pmm.pid = sc.pid1 or pmm.pid = sc.pid2) and sc.status = 'A')
        and fw.rollup_drug_db = $drugDb
        and fw.version = $version
    """

    val pbmFilter = pbm.map(p => fr"and pmm.pid = $p").getOrElse(fr"")
    val typeFilter = `type`.map(t => fr"and fl.type = $t").getOrElse(fr"")
    val subtypeFilter = subtype.map(s => fr"and fl.sub_type = $s").getOrElse(fr"")
    val createdAfterFilter = createdAfter.map(ca => fr"and fw.creation_date >= $ca").getOrElse(fr"")
    val lastModifiedAfterFilter = lastModifiedAfter.map(lma => fr"and fw.last_modified_date >= $lma").getOrElse(fr"")
    val dateFilter = fr"and fl.effective_date <= $today"

    val fullQuery = baseQuery ++ pbmFilter ++ typeFilter ++ subtypeFilter ++ createdAfterFilter ++ lastModifiedAfterFilter ++ dateFilter ++ fr"order by fl.effective_date desc"

    executeQueryFuture[DocumentIdsFileList](fullQuery)
  }
}




#DocumentIdsFileListService

package com.surescripts.formularydownloadapi.documentIdsfilelist

import cats.effect.IO
import com.surescripts.formularydownloadapi.Env
import com.surescripts.scala.cats.data.DataService

import scala.util.Try

case class DocumentIdsFileListService() extends DataService {
  def findDocumentIdsFileList(pbm: Option[String], `type`: Option[String], subtype: Option[String], createdAfter: Option[String], lastModifiedAfter: Option[String], drugDb: String, version: String)(implicit env: Env): IO[Try[Option[DocumentIdsFileList]]] =
    singleQuery[DocumentIdsFileList](env.documentIdsFileListRepository.findDocumentIdsFileList(pbm, `type`, subtype, createdAfter, lastModifiedAfter, drugDb, version))
}


#Controller 

package com.surescripts.formularydownloadapi

import cats.effect.IO
import com.surescripts.formularydownloadapi.documentIdsfilelist.{DocumentIdsFileList, DocumentIdsFileListService}
import io.circe.Json
import io.circe.syntax.EncoderOps
import io.swagger.v3.oas.annotations.media.{ArraySchema, Content, Schema}
import io.swagger.v3.oas.annotations.responses.{ApiResponse, ApiResponses}
import org.springframework.http.ResponseEntity
import org.springframework.web.bind.annotation.{GetMapping, RequestMapping, RequestParam, RestController}
import org.typelevel.log4cats.slf4j.Slf4jLogger

import scala.util.{Failure, Success}
import scala.concurrent.ExecutionContext

@RestController
@RequestMapping(Array("/formulary"))
class Controller(env: Env)(implicit ec: ExecutionContext) {
  implicit val logger = Slf4jLogger.getLogger[IO]

  @ApiResponses(value = Array(
    new ApiResponse(responseCode = "200", description = "Found the lists",
      content = Array(new Content(mediaType = "application/json", schema = new Schema(implementation = classOf[DocumentIdsFileList])))),
    new ApiResponse(responseCode = "404", description = "Lists not found", content = Array(new Content()))))
  @GetMapping(value = Array("/lists"))
  def getDocumentIdsFileList(@RequestParam(required = false) pbm: Option[String], @RequestParam(required = false) `type`: Option[String], @RequestParam(required = false) subtype: Option[String], @RequestParam(required = false) createdafter: Option[String], @RequestParam(required = false) lastmodifiedafter: Option[String]): IO[ResponseEntity[Json]] = {
    env.documentIdsFileListService.findDocumentIdsFileList(pbm, `type`, subtype, createdafter, lastmodifiedafter, "NONE", "60")(env).map {
      case Success(lists) if lists.nonEmpty => ResponseEntity.ok().body(Map("lists" -> lists).asJson)
      case Success(_) => ResponseEntity.status(404).body("No lists found.".asJson)
      case Failure(e) =>
        logger.error(e)("Error calling GET /lists")
        ResponseEntity.internalServerError().body("Internal server error".asJson)
    }
  }

  @GetMapping(value = Array("/shutdown"))
  def shutdown(): IO[Unit] = IO {
    logger.info("Shutting down service.")
    System.exit(0)
  }
}

[Yesterday 5:04 PM] Pawar, Ankit
Formulary Load Mapping
document_name => "name",
PART_NM => "pbm"
list_type => "type"
document_size => "size"
created_date => "created"
last_modified_date => "lastmodified"  If earlier than the created date used created date check how this affect the query
 
Query Parameter Mapping
pbm => Part_NM (if inclued PART_TYPE = ‘PBM’)
type => list_type
subtype => sub_type (I’m not sure if we will need this)
createdafter => created_date
lastmodifiedafter => last_modified_date
[Yesterday 5:04 PM] Pawar, Ankit
Use this query with effective_date and using NONE and 60 for drugdb/version.
effective_date -> effectiveDate
effective_date  will map to the effectiveDate result. Let’s skip the expirationDate field in the response for now. We might need to use a formulary_load_v60 if we want expiration_date but that table isn’t loaded with data yet.

----

package com.surescripts.formularydownloadapi.documentIdsfilelist

import com.fasterxml.jackson.annotation.JsonProperty.Access
import com.fasterxml.jackson.annotation.{JsonCreator, JsonProperty}
import io.circe.derivation.{deriveDecoder, deriveEncoder}
import io.circe.{Decoder, Encoder}

@JsonCreator
case class DocumentIdsFileList(
  @JsonProperty(value = "name", access = Access.READ_WRITE) name: String,
  @JsonProperty(value = "pbm", access = Access.READ_WRITE) pbm: String,
  @JsonProperty(value = "pbmtype", access = Access.READ_WRITE) pbmtype: String,
  @JsonProperty(value = "subtype", access = Access.READ_WRITE) subtype: Option[String],
  @JsonProperty(value = "size", access = Access.READ_WRITE) size: Int,
  @JsonProperty(value = "created", access = Access.READ_WRITE) created: String,
  @JsonProperty(value = "lastmodified", access = Access.READ_WRITE) lastModified: String,
  @JsonProperty(value = "effective_date", access = Access.READ_WRITE) effectiveDate: String
)

object DocumentIdsFileList {
  implicit val decoder: Decoder[DocumentIdsFileList] = deriveDecoder[DocumentIdsFileList]
  implicit val encoder: Encoder[DocumentIdsFileList] = deriveEncoder[DocumentIdsFileList]
}






package com.surescripts.formularydownloadapi.documentIdsfilelist

import com.surescripts.formularydownloadapi.Env
import com.surescripts.scala.cats.data.DataRepository
import doobie.implicits.toSqlInterpolator
import io.github.resilience4j.bulkhead.annotation.Bulkhead
import io.github.resilience4j.bulkhead.annotation.Bulkhead.Type
import io.github.resilience4j.circuitbreaker.annotation.CircuitBreaker
import io.github.resilience4j.ratelimiter.annotation.RateLimiter
import io.github.resilience4j.retry.annotation.Retry
import io.github.resilience4j.timelimiter.annotation.TimeLimiter
import org.springframework.stereotype.Repository

import java.time.LocalDate
import java.time.format.DateTimeFormatter
import java.util.concurrent.CompletableFuture
import scala.util.Try

@Repository
class DocumentIdsFileListRepository extends DataRepository {
  @Bulkhead(name = "default-bulkhead-db", `type` = Type.THREADPOOL)
  @TimeLimiter(name = "default-time-limiter-db")
  @CircuitBreaker(name = "default-circuit-breaker-db")
  @RateLimiter(name = "default-rate-limiter-db")
  @Retry(name = "default-retry-db")
  private val today: String = DateTimeFormatter.BASIC_ISO_DATE.format(LocalDate.now)

  def findDocumentIdsFileList(pbm: Option[String], pbmtype: Option[String], subtype: Option[String], createdAfter: Option[String], lastModifiedAfter: Option[String], drugDb: String, version: String)(implicit env: Env): CompletableFuture[Try[List[DocumentIdsFileList]]] = {
    val baseQuery = sql"""
      select 
        fw.document_name as name,
        pmm.PART_NM as pbm,
        fl.list_type as pbmtype,
        fl.sub_type,
        fw.document_size as size,
        fw.creation_date as created,
        CASE 
          WHEN fw.last_modified_date < fw.creation_date THEN fw.creation_date
          ELSE fw.last_modified_date
        END as lastmodified,
        fl.effective_date as effectiveDate
      from formulary_webdav fw
      inner join formulary_load fl on fw.formulary_load_id = fl.formulary_load_id and fl.management_status = 'A'
      inner join pmm_part pmm on fl.publisher = pmm.part_uid
      where exists(select * from pmm_service_contract sc
      inner join tp_service_direction sd on sc.service_id = sd.service_id and sd.direction = 'R'
      inner join tp_trx_in_service tis on sd.service_direction_id = tis.service_direction_id and tis.trx_type = 'FRMDST'
      where (pmm.pid = sc.pid1 or pmm.pid = sc.pid2) and sc.status = 'A')
        and fw.rollup_drug_db = $drugDb
        and fw.version = $version
    """

    val pbmFilter = pbm.map(p => fr"and pmm.pid = $p").getOrElse(fr"")
    val pbmtypeFilter = pbmtype.map(t => fr"and fl.list_type = $t").getOrElse(fr"")
    val subtypeFilter = subtype.map(s => fr"and fl.sub_type = $s").getOrElse(fr"")
    val createdAfterFilter = createdAfter.map(ca => fr"and fw.creation_date >= $ca").getOrElse(fr"")
    val lastModifiedAfterFilter = lastModifiedAfter.map(lma => fr"and fw.last_modified_date >= $lma").getOrElse(fr"")
    val dateFilter = fr"and fl.effective_date <= $today"

    val fullQuery = baseQuery ++ pbmFilter ++ pbmtypeFilter ++ subtypeFilter ++ createdAfterFilter ++ lastModifiedAfterFilter ++ dateFilter ++ fr"order by fl.effective_date desc"

    executeQueryFuture[DocumentIdsFileList](fullQuery)
  }
}









package com.surescripts.formularydownloadapi.documentIdsfilelist

import cats.effect.IO
import com.surescripts.formularydownloadapi.Env
import com.surescripts.scala.cats.data.DataService

import scala.util.Try

case class DocumentIdsFileListService() extends DataService {
  def findDocumentIdsFileList(pbm: Option[String], pbmtype: Option[String], subtype: Option[String], createdAfter: Option[String], lastModifiedAfter: Option[String], drugDb: String, version: String)(implicit env: Env): IO[Try[Option[DocumentIdsFileList]]] =
    singleQuery[DocumentIdsFileList](env.documentIdsFileListRepository.findDocumentIdsFileList(pbm, pbmtype, subtype, createdAfter, lastModifiedAfter, drugDb, version))
}










package com.surescripts.formularydownloadapi

import cats.effect.IO
import com.surescripts.formularydownloadapi.documentIdsfilelist.{DocumentIdsFileList, DocumentIdsFileListService}
import io.circe.Json
import io.circe.syntax.EncoderOps
import io.swagger.v3.oas.annotations.media.{ArraySchema, Content, Schema}
import io.swagger.v3.oas.annotations.responses.{ApiResponse, ApiResponses}
import org.springframework.http.ResponseEntity
import org.springframework.web.bind.annotation.{GetMapping, RequestMapping, RequestParam, RestController}
import org.typelevel.log4cats.slf4j.Slf4jLogger

import scala.util.{Failure, Success}
import scala.concurrent.ExecutionContext

@RestController
@RequestMapping(Array("/formulary"))
class Controller(env: Env)(implicit ec: ExecutionContext) {
  implicit val logger = Slf4jLogger.getLogger[IO]

  @ApiResponses(value = Array(
    new ApiResponse(responseCode = "200", description = "Found the lists",
      content = Array(new Content(mediaType = "application/json", schema = new Schema(implementation = classOf[DocumentIdsFileList])))),
    new ApiResponse(responseCode = "404", description = "Lists not found", content = Array(new Content()))))
  @GetMapping(value = Array("/lists"))
  def getDocumentIdsFileList(@RequestParam(required = false) pbm: Option[String], @RequestParam(required = false) pbmtype: Option[String], @RequestParam(required = false) subtype: Option[String], @RequestParam(required = false) createdafter: Option[String], @RequestParam(required = false) lastmodifiedafter: Option[String]): IO[ResponseEntity[Json]] = {
    env.documentIdsFileListService.findDocumentIdsFileList(pbm, pbmtype, subtype, createdafter, lastmodifiedafter, "NONE", "60")(env).map {
      case Success(lists) if lists.nonEmpty => ResponseEntity.ok().body(Map("lists" -> lists).asJson)
      case Success(_) => ResponseEntity.status(404).body("No lists found.".asJson)
      case Failure(e) =>
        logger.error(e)("Error calling GET /lists")
        ResponseEntity.internalServerError().body("Internal server error".asJson)
    }
  }

  @GetMapping(value = Array("/shutdown"))
  def shutdown(): IO[Unit] = IO {
    logger.info("Shutting down service.")
    System.exit(0)
  }
}

package com.surescripts.formularydownloadapi

import org.junit.jupiter.api.Test
import org.junit.jupiter.api.extension.ExtendWith
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.http.MediaType
import org.springframework.test.context.junit.jupiter.SpringExtension
import org.springframework.test.web.servlet.MockMvc
import org.springframework.test.web.servlet.request.MockMvcRequestBuilders.get
import org.springframework.test.web.servlet.result.MockMvcResultHandlers.print
import org.springframework.test.web.servlet.result.MockMvcResultMatchers.{jsonPath, status}

@ExtendWith(Array(classOf[SpringExtension]))
@SpringBootTest
@AutoConfigureMockMvc
class ControllerTest {

  @Autowired
  var mockMvc: MockMvc = _

  @Test
  def testDocumentIdsFoundCheck(): Unit = {
    val pbm = "RFS_FPS_PBM3"
    val pbmtype = "ALT"
    val createdAfter = "2021-01-01T00:00:00Z"
    val lastModifiedAfter = "2021-01-01T00:00:00Z"

    this.mockMvc.perform(
      get("/formulary/lists")
        .param("pbm", pbm)
        .param("pbmtype", pbmtype)
        .param("createdafter", createdAfter)
        .param("lastmodifiedafter", lastModifiedAfter)
        .contentType(MediaType.APPLICATION_JSON)
    )
      .andDo(print())
      .andExpect(status().isOk())
      .andExpect(jsonPath("$.lists").isArray)
      .andExpect(jsonPath("$.lists[0].pbm").value(pbm))
      .andExpect(jsonPath("$.lists[0].pbmtype").value(pbmtype))
  }

  @Test
  def testDocumentIdsNotFound(): Unit = {
    val pbm = "INVALID_PBM"
    val pbmtype = "INVALID_TYPE"
    val createdAfter = "2021-01-01T00:00:00Z"
    val lastModifiedAfter = "2021-01-01T00:00:00Z"

    this.mockMvc.perform(
      get("/formulary/lists")
        .param("pbm", pbm)
        .param("pbmtype", pbmtype)
        .param("createdafter", createdAfter)
        .param("lastmodifiedafter", lastModifiedAfter)
        .contentType(MediaType.APPLICATION_JSON)
    )
      .andDo(print())
      .andExpect(status().isNotFound())
  }
}

package com.surescripts.formularydownloadapi

import org.junit.jupiter.api.Test
import org.junit.jupiter.api.extension.ExtendWith
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.http.MediaType
import org.springframework.test.context.junit.jupiter.SpringExtension
import org.springframework.test.web.servlet.MockMvc
import org.springframework.test.web.servlet.request.MockMvcRequestBuilders.get
import org.springframework.test.web.servlet.result.MockMvcResultHandlers.print
import org.springframework.test.web.servlet.result.MockMvcResultMatchers.{jsonPath, status}

@ExtendWith(Array(classOf[SpringExtension]))
@SpringBootTest
@AutoConfigureMockMvc
class ControllerTest {

  @Autowired
  var mockMvc: MockMvc = _

  @Test
  def testDocumentIdsFoundCheck(): Unit = {
    val pbm = "RFS_FPS_PBM3"
    val pbmtype = "ALT"
    val createdAfter = "2021-01-01T00:00:00Z"
    val lastModifiedAfter = "2021-01-01T00:00:00Z"

    this.mockMvc.perform(
      get("/formulary/lists")
        .param("pbm", pbm)
        .param("pbmtype", pbmtype)
        .param("createdafter", createdAfter)
        .param("lastmodifiedafter", lastModifiedAfter)
        .contentType(MediaType.APPLICATION_JSON)
    )
      .andDo(print())
      .andExpect(status().isOk())
      .andExpect(jsonPath("$.lists").isArray)
      .andExpect(jsonPath("$.lists[0].pbm").value(pbm))
      .andExpect(jsonPath("$.lists[0].pbmtype").value(pbmtype))
  }

  @Test
  def testDocumentIdsNotFound(): Unit = {
    val pbm = "INVALID_PBM"
    val pbmtype = "INVALID_TYPE"
    val createdAfter = "2021-01-01T00:00:00Z"
    val lastModifiedAfter = "2021-01-01T00:00:00Z"

    this.mockMvc.perform(
      get("/formulary/lists")
        .param("pbm", pbm)
        .param("pbmtype", pbmtype)
        .param("createdafter", createdAfter)
        .param("lastmodifiedafter", lastModifiedAfter)
        .contentType(MediaType.APPLICATION_JSON)
    )
      .andDo(print())
      .andExpect(status().isNotFound())
  }
}