I want to write the XQuery to print/iterate the keys and values in JSON

Question

This is my sample JSON

{
"id":"743",
"groupName":"group1",
"transation":{
    "101":"success",
    "102":"rejected",
    "301":"processing"
     }
}

Expected Result:

"101":"success",
"102":"rejected",
"301":"processing"

Can anyone please help me to print the above result using for loop in XQuery?

Answer 1

If it's a JSON document, then you can use XPath and do things even easier:

(: read the doc (update to whatever URI) :)
let $json-doc := doc("/test.json")

for $node in $json-doc/transation/node()
return '"'||$node/name()||'":"'||$node||'"'

Or if you have a JSON string to parse to a JSON object, you can use the map:* functions to select and traverse the JSON object:

let $json-doc := xdmp:from-json-string('{ "id":"743", "groupName":"group1", "transation":{ "101":"success", "102":"rejected", "301":"processing" } }')
let $transation := map:get($json-doc, "transation")
for $key in map:keys($transation)
let $value := map:get($transation, $key)
return '"'||$key||'":"'||$value||'"'

If you wanted the JSON document from the database to be a JSON object to use the map functions, you can use the xdmp:from-json() function:

let $json-doc := doc("/test.json") => xdmp:from-json()
let $transation := map:get($json-doc, "transation")
for $key in map:keys($transation)
let $value := map:get($transation, $key)
return '"'||$key||'":"'||$value||'"'