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

Notifications

Announcements

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
    12,982 Super User 2025 Season 2 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,452 Super User 2025 Season 2 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

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 525 Super User 2025 Season 2

#2
Tomac Profile Picture

Tomac 324 Moderator

#3
abm abm Profile Picture

abm abm 232 Most Valuable Professional

Last 30 days Overall leaderboard