Scriptrunner Select List Conversion json query not working

[Therese Hedberg]

(I have no prior knowledge of Groovy nor json so any beginner's mistakes below is due to that)

I am modifying the example code on https://scriptrunner.adaptavist.com/latest/jira/behaviours-conversions.html#_walkthrough_external_rest_service to query a json file that resides on one of our servers. The json file is a snippet from a larger file that creates a view of our systems. The plan is to read the larger source json file a soon as I can get the script to work.

This is the current contents of my json:

{
  "_embedded" : {
    "zystems" : [ {
      "id" : 1,
      "name" : "Mynewsdesk",
      "beskrivning" : "",
      "forvaltning" : {
        "id" : 13,
        "name" : "Avdelning: Kommunikation och externa relationer"
      },
      "inforandeAr" : "",
      "antalAnv" : "<10",
      "systemTyp" : {
        "id" : 1,
        "name" : "Standardsystem"
      },
      "kostnadLicens" : "",
      "kostnadDrift" : "",
      "kostnadUtv" : "",
      "kostnadPersonal" : "",
      "comment" : "",
      "slaLank" : "",
      "forvaltningsplanLank" : "",
      "driftDokumentationLank" : "",
      "systemKartaLank" : "",
      "utvDokumentationLank" : "",
      "dbBeskrivningLank" : "",
      "annanDokLank" : "https://jira.its.uu.se/jira/browse/SYS-155",
      "arbetsFloden" : "",
      "inomVilkaDelarAvOrg" : "",
      "uppdaterad" : null,
      "uppdateradAv" : "",
      "syfte" : [ ],
      "funktion" : [ ],
      "roll" : [ ],
      "informationsInnehall" : [ {
        "id" : 13,
        "name" : "Digital fritext"
      } ],
      "verksamhetsOmrade" : [ {
        "id" : 4,
        "name" : "Samverkan"
      } ],
      "_links" : {
        "self" : {
          "href" : ""
        },
        "servers" : {
          "href" : ""
        },
        "kontaktPerson" : {
          "href" : ""
        },
        "flodeUt" : {
          "href" : ""
        },
        "flodeIn" : {
          "href" : ""
        }
      }
    }, {
      "id" : 2,
      "name" : "Nyhetsbanken",
      "beskrivning" : "System för nyhetspublicering",
      "forvaltning" : {
        "id" : 10,
        "name" : "E-område: Gemensamma webbfunktioner"
      },
      "inforandeAr" : "2009",
      "antalAnv" : "ca 50 användare",
      "systemTyp" : {
        "id" : 3,
        "name" : "Egenutvecklat"
      },
      "kostnadLicens" : "",
      "kostnadDrift" : "",
      "kostnadUtv" : "",
      "kostnadPersonal" : "",
      "comment" : "",
      "slaLank" : "",
      "forvaltningsplanLank" : "Finns på Google Docs",
      "driftDokumentationLank" : "",
      "systemKartaLank" : "",
      "utvDokumentationLank" : "",
      "dbBeskrivningLank" : "",
      "annanDokLank" : "https://jira.its.uu.se/jira/browse/SYS-166",
      "arbetsFloden" : "Systemet stödjer nyhetspublicering",
      "inomVilkaDelarAvOrg" : "Kommunikationsavdelningen samt vetenskapsområdena",
      "uppdaterad" : "2015-05-13",
      "uppdateradAv" : "",
      "syfte" : [ {
        "id" : 4,
        "name" : "Sprida information"
      } ],
      "funktion" : [ {
        "id" : 130,
        "name" : "Nyhetspublicering"
      } ],
      "roll" : [ {
        "id" : 5,
        "name" : "Redaktör"
      }, {
        "id" : 4,
        "name" : "Informatör"
      } ],
      "informationsInnehall" : [ {
        "id" : 128,
        "name" : "Nyheter och Pressmeddelanden"
      } ],
      "verksamhetsOmrade" : [ {
        "id" : 129,
        "name" : "Kommunikation"
      }, {
        "id" : 4,
        "name" : "Samverkan"
      } ],
      "_links" : {
        "self" : {
          "href" : ""
        },
        "servers" : {
          "href" : ""
        },
        "kontaktPerson" : {
          "href" : ""
        },
        "flodeUt" : {
          "href" : ""
        },
        "flodeIn" : {
          "href" : ""
        }
      }
    }
]
  }
}

and this is what my script looks like when modified:

import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import groovy.json.JsonBuilder
import groovy.transform.BaseScript
import groovyx.net.http.ContentType
import groovyx.net.http.HTTPBuilder
import groovyx.net.http.Method

import javax.ws.rs.core.MultivaluedMap
import javax.ws.rs.core.Response

@BaseScript CustomEndpointDelegate delegate

listaSystem(httpMethod: "GET") { MultivaluedMap queryParams ->

def query = queryParams.getFirst("query") as String

def rt = [:]
 String url = "http://files.webb.uu.se"
 String path = "xxx"
 if (query) {
 def httpBuilder = new HTTPBuilder(url)
 def systems = httpBuilder.request(Method.GET, ContentType.JSON) {
 uri.path = path
 uri.query = [q:"$query in:name", sort: "name", order:"desc"]
 headers.'User-Agent' = "Mozilla/5.0 Firefox/3.0.4"
 headers.Accept = 'application/json'

response.failure = { resp, reader ->
 log.warn("Failed to query API: " + reader.text)
 }
 }

def systemNames = systems["zystems"]*."name"
 rt = [
 items: systemNames.collect { String sys ->
 [
 value: sys,
 html : sys.replaceAll(/(?i)$query/) { "<b>${it}</b>" },
 label: sys,
 ]
 },
 total: systems["total_count"],
 //footer: "Välj system... (${systemNames.size()} av ${systems["total_count"]} visas...)"
 ]
 }

return Response.ok(new JsonBuilder(rt).toString()).build()
}

The result I am getting when querying (https://jira-test.its.uu.se/rest/scriptrunner/latest/custom/listaSystem?query=nyhet) is empty:

{"items":[],"total":null}

Where do I go wrong? Is the json badly structured or do I modify the original example incorrectly?

[Therese Hedberg · Accepted solution]

Solved! I had a syntax error where I suspected. And since I don't query an API but read a json file directly I had to add some filtering code:

import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import groovy.json.JsonBuilder
import groovy.transform.BaseScript
import groovyx.net.http.ContentType
import groovyx.net.http.HTTPBuilder
import groovyx.net.http.Method

import javax.ws.rs.core.MultivaluedMap
import javax.ws.rs.core.Response

@BaseScript CustomEndpointDelegate delegate

listaSystem(httpMethod: "GET") { MultivaluedMap queryParams -> 
 def query = queryParams.getFirst("query") as String

def stm = [:]
 String url = "http://www.xyz.com"
 String path = "xyz"
 if (query) {
 def httpBuilder = new HTTPBuilder(url)
 def systems = httpBuilder.request(Method.GET, ContentType.JSON) {
 uri.path = path

response.failure = { resp, reader ->
 log.warn("Failed to query: " + reader.text)
 }
 }


 def systemNames = 
 systems["_embedded"]["zystems"]*."name"
 def filteredNames = 
 systemNames
 .findAll({ it.matches("(?i).*" + query + ".*")})
 .sort()
 
 stm = [
 items: filteredNames.collect { String name ->
 [
 value: name,
 html: name.replaceAll(/(?i)$query/) { "<b>${it}</b>" }, 
 label: name,
 ]
 },
 total: systemNames.size(),
 footer: "Välj system... (${filteredNames.size()} av ${systemNames.size()} visas...)"
 ]
 }

return Response.ok(new JsonBuilder(stm).toString()).build()
}

[Therese Hedberg · Accepted solution]

Solved! I had a syntax error where I suspected. And since I don't query an API but read a json file directly I had to add some filtering code: 

import com.onresolve.scriptrunner.runner.rest.common.CustomEndpointDelegate
import groovy.json.JsonBuilder
import groovy.transform.BaseScript
import groovyx.net.http.ContentType
import groovyx.net.http.HTTPBuilder
import groovyx.net.http.Method

import javax.ws.rs.core.MultivaluedMap
import javax.ws.rs.core.Response

@BaseScript CustomEndpointDelegate delegate

listaSystem(httpMethod: "GET") { MultivaluedMap queryParams -> 
 def query = queryParams.getFirst("query") as String

def stm = [:]
 String url = "http://www.xyz.com"
 String path = "xyz"
 if (query) {
 def httpBuilder = new HTTPBuilder(url)
 def systems = httpBuilder.request(Method.GET, ContentType.JSON) {
 uri.path = path

response.failure = { resp, reader ->
 log.warn("Failed to query: " + reader.text)
 }
 }

 
 def systemNames = 
 systems["_embedded"]["zystems"]*."name"
 def filteredNames = 
 systemNames
 .findAll({ it.matches("(?i).*" + query + ".*")})
 .sort()
 
 stm = [
 items: filteredNames.collect { String name ->
 [
 value: name,
 html: name.replaceAll(/(?i)$query/) { "<b>${it}</b>" }, 
 label: name,
 ]
 },
 total: systemNames.size(),
 footer: "Välj system... (${filteredNames.size()} av ${systemNames.size()} visas...)"
 ]
 }

return Response.ok(new JsonBuilder(stm).toString()).build()
}

[Mark Markov]

Hello @Therese Hedberg

Yes, your json are broken. Try to use https://jsoneditoronline.org/ to create valid json.

I check it here and json results empty arrays.

[Therese Hedberg]

Thank you for your quick feedback @Mark Markov! I will have a closer look at the structure of our json.

Apart from that, do you (or someone else) know how to describe how to assign the following variable correctly? (example from the scriptrunner doc). Are "items" the name of an array and "full_name" one of the values in the array? What if the array is nested? Or is "items" the object? (as I said, I am a newbie at json ;-))

def repoNames = repos["items"]*."full_name"

[Therese Hedberg]

Hi again @Mark Markov

How did you get the result that the json returns empty arrays? When I validate it, it comes back ok; https://jsoneditoronline.org/?url=http%3A%2F%2Ffiles.webb.uu.se%2Fuploader%2F771%2Fzystems-kort

Is it maybe on these lines that I make the mistakes?

uri.query = [q:"$query in:name", sort: "name", order:"desc"]

def systemNames = systems["zystems"]*."name"