web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Automate
Suggested Answer

Database / parse JSON

(1) ShareShare
ReportReport
Posted on by 4
I'm absolutely sure I'm missing something obvious here.
 
We have an externally-hosted MySQL database with an API to connect into it.
 
In PowerBI this is working as expected, so I am fairly confident the API is good.
 
However, in Power Automate I'm trying to build a flow to use this data. The API is being used via a HTTP action which seems to work, the data is pulled from the database as expected:
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
I am then passing the output from this into a parse JSON action. Again, this seems to work with the scheme generated from a sample payload taken from the output from the previous action, the output seems to hold all the data expected:
 
 
 
However, once this is in place I cannot get any of the fields to show in the dynamic content to then use in subsequent actions.
 
 
 
This is the same regardless of which action is being used.
 
 
I'm sure this is something daft, but I just can't get my head around it and it's driving me crosseyed.
 
All suggestions welcomed!
 
 
Categories:
I have the same question (0)
  • Suggested answer
    David_MA Profile Picture
    14,485 Super User 2026 Season 1 on at
    Put the Item value into a Compose action, run your flow and then use the body of the Compose action in another Parse JSON action. There is probably a better way to do this, but this works for me. Otherwise, you'll need to parse the value out of the JSON using an expression as explained here: How to get a specific value from a JSON in Power Automate (tomriha.com)
  • Suggested answer
    Michael E. Gernaey Profile Picture
    53,978 Moderator on at
    HI
     
    Would need to set the full JSON.
     
    Also are you putting this in an Apply to Each, or are you trying to write directly to a record? Right now it says its an Array, which stinks because you cannot access properties in an Array the same way as normal. So essentially it cannot show you what you want and how you want it.
     
    please share the JSON and we may be able to help convert it to what you are looking for.I don't care if you change the values, but dont change the JSON schema pls
  • AH-23081321-0 Profile Picture
    4 on at
    Thanks all
     
    Here's the JSON schema:
     
    {
    "type": "object",
    "properties": {
    "statusCode": {
    "type": "integer"
    },
    "headers": {
    "type": "object",
    "properties": {
    "Server": {
    "type": "string"
    },
    "Transfer-Encoding": {
    "type": "string"
    },
    "Connection": {
    "type": "string"
    },
    "Vary": {
    "type": "string"
    },
    "Cache-Control": {
    "type": "string"
    },
    "Date": {
    "type": "string"
    },
    "X-RateLimit-Limit": {
    "type": "string"
    },
    "X-RateLimit-Remaining": {
    "type": "string"
    },
    "Access-Control-Allow-Origin": {
    "type": "string"
    },
    "X-Frame-Options": {
    "type": "string"
    },
    "X-XSS-Protection": {
    "type": "string"
    },
    "X-Content-Type-Options": {
    "type": "string"
    },
    "Content-Type": {
    "type": "string"
    },
    "Content-Length": {
    "type": "string"
    }
    }
    },
    "body": {
    "type": "array",
    "items": {
    "type": "object",
    "properties": {
    "id": {
    "type": "integer"
    },
    "company_id": {
    "type": "integer"
    },
    "reference": {
    "type": "string"
    },
    "title": {
    "type": "string"
    },
    "first_name": {
    "type": "string"
    },
    "last_name": {
    "type": "string"
    },
    "name": {
    "type": "string"
    },
    "gender": {
    "type": "string"
    },
    "date_of_birth": {
    "type": "string"
    },
    "email": {
    "type": "string"
    },
    "phone": {
    "type": "string"
    },
    "student_id": {},
    "ni_number": {
    "type": "string"
    },
    "emergency_contact_name": {
    "type": "string"
    },
    "emergency_contact_number": {
    "type": "string"
    },
    "house_number": {
    "type": "string"
    },
    "address_line_2": {
    "type": "string"
    },
    "city": {
    "type": "string"
    },
    "state": {
    "type": "string"
    },
    "postal_code": {
    "type": "string"
    },
    "address_country": {
    "type": "string"
    },
    "same_as": {
    "type": "string"
    },
    "current_house_number": {
    "type": "string"
    },
    "current_address_line_2": {
    "type": "string"
    },
    "current_city": {
    "type": "string"
    },
    "current_state": {
    "type": "string"
    },
    "current_postal_code": {
    "type": "string"
    },
    "current_country": {
    "type": "string"
    },
    "nationality": {
    "type": "string"
    },
    "other_nationality": {},
    "visa_category": {},
    "date_entry_of_uk": {},
    "ethnic_origin": {},
    "university_id": {
    "type": "integer"
    },
    "campus_id": {
    "type": "integer"
    },
    "course_id": {
    "type": "integer"
    },
    "local_course_fee": {
    "type": "string"
    },
    "international_course_fee": {
    "type": "string"
    },
    "intake": {
    "type": "string"
    },
    "delivery_pattern": {
    "type": "string"
    },
    "slug": {},
    "admission_officer_id": {
    "type": "integer"
    },
    "marketing_officer_id": {
    "type": "integer"
    },
    "manager_id": {
    "type": "integer"
    },
    "interviewer_id": {
    "type": "integer"
    },
    "interview_status": {
    "type": "integer"
    },
    "is_academic": {
    "type": "integer"
    },
    "application_status_id": {
    "type": "integer"
    },
    "is_final_interview": {
    "type": "integer"
    },
    "app_process": {
    "type": "integer"
    },
    "is_written_test": {
    "type": "integer"
    },
    "conditional_offer_text": {},
    "create_by": {
    "type": "integer"
    },
    "update_by": {
    "type": "integer"
    },
    "steps": {
    "type": "string"
    },
    "application_process_status": {
    "type": "integer"
    },
    "application_type": {
    "type": "integer"
    },
    "status": {
    "type": "integer"
    },
    "is_deleted": {
    "type": "integer"
    },
    "created_at": {
    "type": "string"
    },
    "updated_at": {
    "type": "string"
    },
    "step2_data": {
    "type": "object",
    "properties": {
    "id": {
    "type": "integer"
    },
    "application_id": {
    "type": "integer"
    },
    "disabilities": {
    "type": "string"
    },
    "criminal_convictions": {
    "type": "string"
    },
    "status": {
    "type": "integer"
    },
    "created_at": {
    "type": "string"
    },
    "updated_at": {
    "type": "string"
    }
    }
    },
    "step3_data": {
    "type": "object",
    "properties": {
    "id": {
    "type": "integer"
    },
    "application_id": {
    "type": "integer"
    },
    "status": {
    "type": "integer"
    },
    "created_at": {
    "type": "string"
    },
    "updated_at": {
    "type": "string"
    }
    }
    },
    "application_documents": {
    "type": "array",
    "items": {
    "type": "object",
    "properties": {
    "id": {
    "type": "integer"
    },
    "application_id": {
    "type": "integer"
    },
    "document_type": {
    "type": "string"
    },
    "title": {},
    "doc": {
    "type": "string"
    },
    "is_view": {
    "type": "integer"
    },
    "user_id": {
    "type": "integer"
    },
    "create_date": {},
    "created_at": {
    "type": "string"
    },
    "updated_at": {
    "type": "string"
    }
    },
    "required": [
    "id",
    "application_id",
    "document_type",
    "title",
    "doc",
    "is_view",
    "user_id",
    "create_date",
    "created_at",
    "updated_at"
    ]
    }
    },
    "campus": {
    "type": "object",
    "properties": {
    "id": {
    "type": "integer"
    },
    "country": {
    "type": "string"
    },
    "slug": {
    "type": "string"
    },
    "university_id": {
    "type": "integer"
    },
    "campus_name": {
    "type": "string"
    },
    "website": {
    "type": "string"
    },
    "monthly_living_cost": {
    "type": "string"
    },
    "funds_requirements_for_visa": {
    "type": "string"
    },
    "application_fee": {
    "type": "string"
    },
    "currency": {
    "type": "string"
    },
    "is_lang_mendatory": {
    "type": "string"
    },
    "lang_requirements": {
    "type": "string"
    },
    "institute_benifits": {
    "type": "string"
    },
    "per_time_work_details": {
    "type": "string"
    },
    "scholarship_policy": {
    "type": "string"
    },
    "institute_important_notes": {
    "type": "string"
    },
    "institute_logo": {
    "type": "string"
    },
    "institute_prospectus": {},
    "institute_course_pdf": {},
    "active": {
    "type": "integer"
    },
    "created_at": {
    "type": "string"
    },
    "updated_at": {
    "type": "string"
    }
    }
    },
    "course": {
    "type": "object",
    "properties": {
    "id": {
    "type": "integer"
    },
    "campus_id": {
    "type": "integer"
    },
    "course_name": {
    "type": "string"
    },
    "category_id": {
    "type": "integer"
    },
    "course_level_id": {
    "type": "integer"
    },
    "course_duration": {
    "type": "string"
    },
    "course_fee": {
    "type": "string"
    },
    "international_course_fee": {
    "type": "string"
    },
    "course_intake": {
    "type": "string"
    },
    "awarding_body": {
    "type": "string"
    },
    "is_lang_mendatory": {
    "type": "string"
    },
    "lang_requirements": {},
    "per_time_work_details": {},
    "addtional_info_course": {},
    "course_prospectus": {},
    "course_module": {},
    "slug": {
    "type": "string"
    },
    "create_by": {
    "type": "integer"
    },
    "update_by": {},
    "status": {
    "type": "integer"
    },
    "created_at": {
    "type": "string"
    },
    "updated_at": {
    "type": "string"
    }
    }
    },
    "assign": {
    "type": "object",
    "properties": {
    "id": {
    "type": "integer"
    },
    "first_name": {
    "type": "string"
    },
    "last_name": {
    "type": "string"
    },
    "name": {
    "type": "string"
    },
    "email": {
    "type": "string"
    },
    "phone": {
    "type": "string"
    },
    "photo": {
    "type": "string"
    },
    "country": {
    "type": "string"
    },
    "state": {},
    "city": {},
    "zip_code": {},
    "address": {},
    "email_verified_at": {},
    "role": {
    "type": "string"
    },
    "slug": {
    "type": "string"
    },
    "company_id": {},
    "create_by": {
    "type": "integer"
    },
    "is_admin": {
    "type": "integer"
    },
    "active": {
    "type": "integer"
    },
    "created_at": {
    "type": "string"
    },
    "updated_at": {
    "type": "string"
    },
    "is_export": {
    "type": "integer"
    }
    }
    },
    "company": {
    "type": "object",
    "properties": {
    "id": {
    "type": "integer"
    },
    "company_name": {
    "type": "string"
    },
    "company_registration_number": {
    "type": "string"
    },
    "company_establish_date": {
    "type": "string"
    },
    "company_trade_license": {},
    "company_trade_license_number": {
    "type": "string"
    },
    "company_email": {
    "type": "string"
    },
    "company_phone": {
    "type": "string"
    },
    "country": {
    "type": "string"
    },
    "state": {
    "type": "string"
    },
    "city": {
    "type": "string"
    },
    "zip_code": {
    "type": "string"
    },
    "address": {
    "type": "string"
    },
    "company_logo": {
    "type": "string"
    },
    "agreement_title": {
    "type": "string"
    },
    "agreement_doc_file": {},
    "agreement_expire_date": {
    "type": "string"
    },
    "status": {
    "type": "integer"
    },
    "created_at": {
    "type": "string"
    },
    "updated_at": {
    "type": "string"
    }
    }
    },
    "university": {
    "type": "object",
    "properties": {
    "id": {
    "type": "integer"
    },
    "title": {
    "type": "string"
    },
    "status": {
    "type": "integer"
    },
    "created_at": {
    "type": "string"
    },
    "updated_at": {
    "type": "string"
    }
    }
    },
    "interviewer": {},
    "sub_agent": {
    "type": "object",
    "properties": {
    "id": {
    "type": "integer"
    },
    "first_name": {
    "type": "string"
    },
    "last_name": {
    "type": "string"
    },
    "name": {
    "type": "string"
    },
    "email": {
    "type": "string"
    },
    "phone": {
    "type": "string"
    },
    "photo": {
    "type": "string"
    },
    "country": {},
    "state": {},
    "city": {},
    "zip_code": {},
    "address": {},
    "email_verified_at": {},
    "role": {
    "type": "string"
    },
    "slug": {
    "type": "string"
    },
    "company_id": {
    "type": "integer"
    },
    "create_by": {},
    "is_admin": {
    "type": "integer"
    },
    "active": {
    "type": "integer"
    },
    "created_at": {
    "type": "string"
    },
    "updated_at": {
    "type": "string"
    },
    "is_export": {
    "type": "integer"
    }
    }
    },
    "application_status": {
    "type": "object",
    "properties": {
    "id": {
    "type": "integer"
    },
    "title": {
    "type": "string"
    },
    "status": {
    "type": "integer"
    },
    "created_at": {
    "type": "string"
    },
    "updated_at": {
    "type": "string"
    }
    }
    },
    "interview_status_data": {}
    },
    "required": [
    "id",
    "company_id",
    "reference",
    "title",
    "first_name",
    "last_name",
    "name",
    "gender",
    "date_of_birth",
    "email",
    "phone",
    "student_id",
    "ni_number",
    "emergency_contact_name",
    "emergency_contact_number",
    "house_number",
    "address_line_2",
    "city",
    "state",
    "postal_code",
    "address_country",
    "same_as",
    "current_house_number",
    "current_address_line_2",
    "current_city",
    "current_state",
    "current_postal_code",
    "current_country",
    "nationality",
    "other_nationality",
    "visa_category",
    "date_entry_of_uk",
    "ethnic_origin",
    "university_id",
    "campus_id",
    "course_id",
    "local_course_fee",
    "international_course_fee",
    "intake",
    "delivery_pattern",
    "slug",
    "admission_officer_id",
    "marketing_officer_id",
    "manager_id",
    "interviewer_id",
    "interview_status",
    "is_academic",
    "application_status_id",
    "is_final_interview",
    "app_process",
    "is_written_test",
    "conditional_offer_text",
    "create_by",
    "update_by",
    "steps",
    "application_process_status",
    "application_type",
    "status",
    "is_deleted",
    "created_at",
    "updated_at",
    "step2_data",
    "step3_data",
    "application_documents",
    "campus",
    "course",
    "assign",
    "company",
    "university",
    "interviewer",
    "sub_agent",
    "application_status",
    "interview_status_data"
    ]
    }
    }
    }
    }
     
     
     
    This was generated from the output of the HTTP action. This is too big to post here in entirety, but here's the first full record (anonymised):
     
    {
      "statusCode": 200,
      "headers": {
        "Server": "nginx",
        "Transfer-Encoding": "chunked",
        "Connection": "keep-alive",
        "Vary": "Accept-Encoding",
        "Cache-Control": "no-cache, private",
        "Date": "Sat, 24 Aug 2024 11:07:23 GMT",
        "X-RateLimit-Limit": "60",
        "X-RateLimit-Remaining": "59",
        "Access-Control-Allow-Origin": "*",
        "X-Frame-Options": "SAMEORIGIN",
        "X-XSS-Protection": "1; mode=block",
        "X-Content-Type-Options": "nosniff",
        "Content-Type": "application/json",
        "Content-Length": "46545247"
      },
      "body": [
        {
          "id": 1,
          "company_id": 1,
          "reference": "NIL",
          "title": "Mrs",
          "first_name": "LLLL",
          "last_name": "RRRR",
          "name": "Mrs LLLL RRRR",
          "gender": "Female",
          "date_of_birth": "1901-01-01",
          "email": "AAAA@GGGG.GG.GG",
          "phone": "0777777777",
          "student_id": null,
          "ni_number": "AB123456C",
          "emergency_contact_name": "sister",
          "emergency_contact_number": "0777777777",
          "house_number": "123 TTTT Road",
          "address_line_2": "JJJJ",
          "city": "Newcastle upon Tyne",
          "state": "England",
          "postal_code": "A12 3BC",
          "address_country": "United Kingdom",
          "same_as": "yes",
          "current_house_number": "129 TTTT Road",
          "current_address_line_2": "JJJJ",
          "current_city": "Newcastle upon Tyne",
          "current_state": "England",
          "current_postal_code": "A12 3BC",
          "current_country": "United Kingdom",
          "nationality": "UK National",
          "other_nationality": null,
          "visa_category": null,
          "date_entry_of_uk": null,
          "ethnic_origin": null,
          "university_id": 1,
          "campus_id": 1,
          "course_id": 2,
          "local_course_fee": "9,250.00",
          "international_course_fee": "9,250.00",
          "intake": "2023-09",
          "delivery_pattern": "Weekdays",
          "slug": null,
          "admission_officer_id": 23,
          "marketing_officer_id": 0,
          "manager_id": 26,
          "interviewer_id": 0,
          "interview_status": 0,
          "is_academic": 1,
          "application_status_id": 1,
          "is_final_interview": 0,
          "app_process": 0,
          "is_written_test": 0,
          "conditional_offer_text": null,
          "create_by": 2,
          "update_by": 1,
          "steps": "1,2,3",
          "application_process_status": 1,
          "application_type": 1,
          "status": 1,
          "is_deleted": 0,
          "created_at": "2023-07-03T09:19:45.000000Z",
          "updated_at": "2024-03-28T15:08:47.000000Z",
          "step2_data": {
            "id": 1,
            "application_id": 1,
            "disabilities": "no",
            "criminal_convictions": "no",
            "status": 0,
            "created_at": "2023-07-03T09:22:17.000000Z",
            "updated_at": "2023-07-03T09:22:17.000000Z"
          },
          "step3_data": {
            "id": 1,
            "application_id": 1,
            "status": 0,
            "created_at": "2023-07-03T09:22:30.000000Z",
            "updated_at": "2023-07-03T09:22:30.000000Z"
          },
          "application_documents": [
            {
              "id": 2,
              "application_id": 1,
              "document_type": "PPPP",
              "title": null,
              "doc": "backend/images/application/doc/1/CCCC.docx",
              "is_view": 1,
              "user_id": 0,
              "create_date": null,
              "created_at": "2023-07-03T09:22:10.000000Z",
              "updated_at": "2023-07-03T09:22:10.000000Z"
            },
            {
              "id": 1,
              "application_id": 1,
              "document_type": "SSSS",
              "title": null,
              "doc": "backend/images/application/doc/1/CCCC.docx",
              "is_view": 1,
              "user_id": 0,
              "create_date": null,
              "created_at": "2023-07-03T09:20:43.000000Z",
              "updated_at": "2023-07-03T09:20:43.000000Z"
            }
          ],
          "campus": {
            "id": 1,
            "country": "United Kingdom",
            "slug": "MMMM",
            "university_id": 1,
            "campus_name": "MMMM",
            "website": "https://UUUU.UU/",
            "monthly_living_cost": "3000",
            "funds_requirements_for_visa": "2000",
            "application_fee": "3000",
            "currency": "£",
            "is_lang_mendatory": "yes",
            "lang_requirements": "English",
            "institute_benifits": "Scholarship",
            "per_time_work_details": "Available",
            "scholarship_policy": "Test Policy",
            "institute_important_notes": "Formal mandatory",
            "institute_logo": "backend/images/campus/campus_logo/UUUU.jpg",
            "institute_prospectus": null,
            "institute_course_pdf": null,
            "active": 1,
            "created_at": "2023-07-01T08:21:19.000000Z",
            "updated_at": "2023-07-01T08:21:19.000000Z"
          },
          "course": {
            "id": 2,
            "campus_id": 1,
            "course_name": "BSc (Hons) BBBB",
            "category_id": 4,
            "course_level_id": 2,
            "course_duration": "4 Years",
            "course_fee": "9,250.00",
            "international_course_fee": "9,250.00",
            "course_intake": "January2024,",
            "awarding_body": "UUUU",
            "is_lang_mendatory": "yes",
            "lang_requirements": null,
            "per_time_work_details": null,
            "addtional_info_course": null,
            "course_prospectus": null,
            "course_module": null,
            "slug": "BBBB",
            "create_by": 54,
            "update_by": null,
            "status": 1,
            "created_at": "2023-09-11T13:39:38.000000Z",
            "updated_at": "2024-03-26T11:43:13.000000Z"
          },
          "assign": {
            "id": 23,
            "first_name": "MMMM",
            "last_name": "HHHH ",
            "name": "MMMM HHHH",
            "email": "AAAA@UUUU.UU",
            "phone": "07777777777",
            "photo": "backend/images/users/admission_officer/MMMM.jpg",
            "country": "United Kingdom",
            "state": null,
            "city": null,
            "zip_code": null,
            "address": null,
            "email_verified_at": null,
            "role": "AAAA",
            "slug": "MMMM-HHHH",
            "company_id": null,
            "create_by": 3,
            "is_admin": 0,
            "active": 0,
            "created_at": "2023-07-10T12:57:53.000000Z",
            "updated_at": "2024-06-14T08:58:55.000000Z",
            "is_export": 0
          },
          "company": {
            "id": 1,
            "company_name": "GGGG",
            "company_registration_number": "12345678",
            "company_establish_date": "2020-01-01",
            "company_trade_license": null,
            "company_trade_license_number": "987654",
            "company_email": "AAAA@GGGG.GG",
            "company_phone": "07777777777",
            "country": "United Kingdom",
            "state": "England",
            "city": "Manchester",
            "zip_code": "A12 3BC",
            "address": "CCCC",
            "company_logo": "backend/images/company/company_logo/GGGG.jpg",
            "agreement_title": "GGGG",
            "agreement_doc_file": null,
            "agreement_expire_date": "2025-01-01 00:00:00",
            "status": 1,
            "created_at": "2023-07-03T08:56:27.000000Z",
            "updated_at": "2023-07-03T08:56:27.000000Z"
          },
          "university": {
            "id": 1,
            "title": "UUUU",
            "status": 0,
            "created_at": "2023-07-01T08:15:21.000000Z",
            "updated_at": "2023-07-01T08:15:21.000000Z"
          },
          "interviewer": null,
          "sub_agent": {
            "id": 2,
            "first_name": "MMMM",
            "last_name": "DDDD ",
            "name": "NNNN PPPP",
            "email": "AAAA@GGGG.GG",
            "phone": "+44 7777777777",
            "photo": "backend/images/users/agent/WWWW.jpeg",
            "country": null,
            "state": null,
            "city": null,
            "zip_code": null,
            "address": null,
            "email_verified_at": null,
            "role": "agent",
            "slug": "MMMM",
            "company_id": 1,
            "create_by": null,
            "is_admin": 1,
            "active": 1,
            "created_at": "2023-07-03T08:56:27.000000Z",
            "updated_at": "2024-02-01T15:24:22.000000Z",
            "is_export": 0
          },
          "application_status": {
            "id": 1,
            "title": "New",
            "status": 0,
            "created_at": "2023-07-03T06:45:18.000000Z",
            "updated_at": "2023-07-03T06:45:18.000000Z"
          },
          "interview_status_data": null
        },
     
     
    Appreciate any further help. Thanks!
     
     
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Users!

Kudos to our 2025 Community Spotlight Honorees

Congratulations to our 2025 community superstars!

Congratulations to the April Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Vish WR Profile Picture

Vish WR 976

#2
Valantis Profile Picture

Valantis 863

#3
Haque Profile Picture

Haque 547

Last 30 days Overall leaderboard