I'm trying to get a list of reviews from an api. This gives me a JSON with nested properties. I manage to get almost all the data I need except from the nested array ["value"]["answers"]["value"] because sometimes the array ["answers"] has one item (with "id" being either "goods", "delivery" or "service"), or 2 or all 3 of them. So I can't use the index to get what I want. I need to be able to check the "id" and from that append the "value" to my array. Is there a way to write an expression that checks the "id" and from then on writes the "value" in my array ?
JSON :
...
"surveyData": [
{
"questionRef": "q1",
"type": "CORE_STAR_RATING",
"properties": {
"title": "How do you evaluate ...?"
},
"value": {
"stars": 5,
"notAnswered": false
},
"userDefinedId": "q1"
},
"userDefinedId": "q2-good"
},
{
"questionRef": "q3",
"type": "DIMENSIONS",
"properties": {
"title": "How do you evaluate on those criterias ?"
},
**"value": {
"answers": [
{
"id": "delivery",
"value": 4,
"userDefinedId": "delivery",
"name": "Delivery",
"firstLabel": "Nul",
"lastLabel": "Good"
},
{
"id": "goods",
"value": 5,
"userDefinedId": "goods",
"name": "Goods",
"firstLabel": "Nul",
"lastLabel": "Good"
},
{
"id": "service",
"value": 5,
"userDefinedId": "service",
"name": "Service",
"firstLabel": "Nul",
"lastLabel": "Good"
}
]**
},
"userDefinedId": "q3-group"
}
]
...
My array so far :
"value": {
"Event": "@items('For_each-Service')?['event']?['type']",
"Delivery": "", <= ??
"Goods": "", <= ??
"Service": "@items('For_each-Service')?['surveyData'][2]?['value']?['answers'][2]?['value']", <= doesn't work
"comment": "@items('For_each-Service')?['reply']?['comment']",
"order date": "@items('For_each-Service')?['transaction']?['date']",
"date": "@items('For_each-Service')?['reply']?['createdAt']",
"email": "@items('For_each-Service')?['customer']?['email']",
"id review": "@items('For_each-Service')?['event']?['id']",
"name client": "@items('For_each-Service')?['customer']?['fullName']",
"status": "@items('For_each-Service')?['state']",
"title": "@items('For_each-Service')?['title']"
}
Thank you
CodePudding user response：
There are a couple of ways to skin this cat, some more efficient than others.
Personally, I would take the following approach.
I created a flow where, basically, the first thing it does is initialise a variable that contains your Answers array as the data ...
The next step is to initialize another variable that stores that array as XML ...
This is the expression contained within ...
xml(json(concat('{ "root": { "answer": ', variables('Answers Array'), '}}')))
In the next step, I'm basically doing what you're doing by constructing an object, only this time, the selection for the values are a little more complex, this is the definition of the body ...
{
"Delivery": @{first(xpath(xml(variables('XML')), '//id[contains(text(), "delivery")]/parent::answer/value/text()'))},
"Goods": @{first(xpath(xml(variables('XML')), '//id[contains(text(), "goods")]/parent::answer/value/text()'))},
"Service": @{first(xpath(xml(variables('XML')), '//id[contains(text(), "service")]/parent::answer/value/text()'))}
}
Using the xpath approach for querying the dataset can cut down the amount of steps you need to take to get the desired result.
If an element is missing, it will simply come back as null ...
CodePudding user response：
Below is one of the workarounds to achieve your requirement. Firstly, I have initialised 3 variables for Delivery, Goods and Service Counts.
Then used Parse_JSON to extract the inner values of the JSON provided.
In the next step I'm trying to iterate through Id's using Switch action with body('Parse_JSON')?['value']?['answers']?[iterationIndexes('Until')]?['id'] expression and set the variables using below expression inside an until loop.
body('Parse_JSON')?['value']?['answers']?[iterationIndexes('Until')]?['value']
and finally you can add in your array like below
RESULTS:
You can use below code view to reproduce in your logic app
{
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"actions": {
"Compose": {
"inputs": {
"value": {
"answers": [
{
"firstLabel": "Nul",
"id": "goods",
"lastLabel": "Good",
"name": "Goods",
"userDefinedId": "goods",
"value": 4
},
{
"firstLabel": "Nul",
"id": "service",
"lastLabel": "Good",
"name": "Service",
"userDefinedId": "service",
"value": 5
}
]
}
},
"runAfter": {
"Initialize_variable_Service_Count": [
"Succeeded"
]
},
"type": "Compose"
},
"Compose_2": {
"inputs": " \"value\": {\n \"Event\": \"@items('For_each-Service')?['event']?['type']\",\n \"Delivery\": \"@{variables('Delivery Count')}\", \n \"Goods\": \"@{variables('Goods Count')}\", \n \"Service\": \"@{variables('Service Count')}\"\n }",
"runAfter": {
"Until": [
"Succeeded"
]
},
"type": "Compose"
},
"Initialize_variable": {
"inputs": {
"variables": [
{
"name": "i",
"type": "integer",
"value": 0
}
]
},
"runAfter": {},
"type": "InitializeVariable"
},
"Initialize_variable_Delivery_count": {
"inputs": {
"variables": [
{
"name": "Delivery Count",
"type": "integer"
}
]
},
"runAfter": {
"Initialize_variable": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_variable_Goods_Count": {
"inputs": {
"variables": [
{
"name": "Goods Count",
"type": "integer"
}
]
},
"runAfter": {
"Initialize_variable_Delivery_count": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_variable_Service_Count": {
"inputs": {
"variables": [
{
"name": "Service Count",
"type": "integer"
}
]
},
"runAfter": {
"Initialize_variable_Goods_Count": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Parse_JSON": {
"inputs": {
"content": "@outputs('Compose')",
"schema": {
"properties": {
"value": {
"properties": {
"answers": {
"items": {
"properties": {
"firstLabel": {
"type": "string"
},
"id": {
"type": "string"
},
"lastLabel": {
"type": "string"
},
"name": {
"type": "string"
},
"userDefinedId": {
"type": "string"
},
"value": {
"type": "integer"
}
},
"required": [],
"type": "object"
},
"type": "array"
}
},
"type": "object"
}
},
"type": "object"
}
},
"runAfter": {
"Compose": [
"Succeeded"
]
},
"type": "ParseJson"
},
"Until": {
"actions": {
"Increment_variable": {
"inputs": {
"name": "i",
"value": 1
},
"runAfter": {
"Switch": [
"Succeeded"
]
},
"type": "IncrementVariable"
},
"Switch": {
"cases": {
"Case_Delivery": {
"actions": {
"Set_variable_Delivery_Count": {
"inputs": {
"name": "Delivery Count",
"value": "@body('Parse_JSON')?['value']?['answers']?[iterationIndexes('Until')]?['value']"
},
"runAfter": {},
"type": "SetVariable"
}
},
"case": "delivery"
},
"Case_Goods": {
"actions": {
"Set_variable_Goods_Count": {
"inputs": {
"name": "Goods Count",
"value": "@body('Parse_JSON')?['value']?['answers']?[iterationIndexes('Until')]?['value']"
},
"runAfter": {},
"type": "SetVariable"
}
},
"case": "goods"
},
"Case_Service": {
"actions": {
"Set_variable_Service_Count": {
"inputs": {
"name": "Service Count",
"value": "@body('Parse_JSON')?['value']?['answers']?[iterationIndexes('Until')]?['value']"
},
"runAfter": {},
"type": "SetVariable"
}
},
"case": "service"
}
},
"default": {
"actions": {}
},
"expression": "@body('Parse_JSON')?['value']?['answers']?[iterationIndexes('Until')]?['id']",
"runAfter": {},
"type": "Switch"
}
},
"expression": "@equals(variables('i'), length(body('Parse_JSON')?['value']?['answers']))",
"limit": {
"count": 10,
"timeout": "PT1H"
},
"runAfter": {
"Parse_JSON": [
"Succeeded"
]
},
"type": "Until"
}
},
"contentVersion": "1.0.0.0",
"outputs": {},
"parameters": {},
"triggers": {
"manual": {
"inputs": {
"schema": {}
},
"kind": "Http",
"type": "Request"
}
}
},
"parameters": {}
}