JSON to CSV via MuleSoft DataWeave - Okta App data

Question

I was struggling to get a DWL script going, given the following JSON data from Okta's API.

Search for only "signOnMode" JSON array items that equal to "SAML_2_0", and ignore/skip "OPENID_CONNECT" and "BROWSER_PLUGIN"
For "SAML_2_0", grab specific components
Transform JSON to CSV (Id, Label, and ssoAcsUrl)

The second part (where I say 'specific components') is where the "ssoAcsUrl" values are embedded in the array syntax (settings ==> signOn ==> ssoAcsUrl)

"signOn":  "@{key1=value1;key2=value2}"

Here is the input:

[
    {
        "id":  "0oa167",
        "orn":  "orn:okta:idp:00o38:apps:oidc_client:0oa167",
        "name":  "oidc_client",
        "label":  "Marketing Page URL",
        "status":  "ACTIVE",
        "lastUpdated":  {
                            "value":  "\/Date(1737521317000)\/",
                            "DisplayHint":  2,
                            "DateTime":  "Tuesday, January 21, 2025 8:48:37 PM"
                        },
        "created":  {
                        "value":  "\/Date(1653425331000)\/",
                        "DisplayHint":  2,
                        "DateTime":  "Tuesday, May 24, 2022 1:48:51 PM"
                    },
        "accessibility":  {
                              "selfService":  false,
                              "errorRedirectUrl":  null,
                              "loginRedirectUrl":  null
                          },
        "visibility":  {
                           "autoLaunch":  false,
                           "autoSubmitToolbar":  false,
                           "hide":  "@{iOS=True; web=True}",
                           "appLinks":  "@{oidc_client_link=True}"
                       },
        "features":  [

                     ],
        "signOnMode":  "OPENID_CONNECT",
        "credentials":  {
                            "userNameTemplate":  "@{template=${source.login}; type=BUILT_IN}",
                            "signing":  "@{kid=X1TggE}",
                            "oauthClient":  "@{autoKeyRotation=True; client_id=0oa167; token_endpoint_auth_method=none; pkce_required=True}"
                        },
        "settings":  {
                         "app":  "",
                         "notifications":  "@{vpn=}",
                         "manualProvisioning":  false,
                         "implicitAssignment":  false,
                         "notes":  "@{admin=; enduser=}",
                         "oauthClient":  "@{client_uri=; logo_uri=; redirect_uris=System.Object[]; response_types=System.Object[]; grant_types=System.Object[]; initiate_login_uri=https://mcz.pub.sfmc.orgcom/bw3g; application_type=browser; consent_method=TRUSTED; issuer_mode=DYNAMIC; idp_initiated_login=; wildcard_redirect=DISABLED; dpop_bound_access_tokens=False}"
                     },
        "_links":  {
                       "uploadLogo":  "@{href=https://dev.okta.com/api/v1/apps/0oa167/logo; hints=}",
                       "appLinks":  "",
                       "profileEnrollment":  "@{href=https://dev.okta.com/api/v1/policies/rst3d2b42qqKIGg87697}",
                       "policies":  "@{href=https://dev.okta.com/api/v1/apps/0oa167/policies; hints=}",
                       "groups":  "@{href=https://dev.okta.com/api/v1/apps/0oa167/groups}",
                       "logo":  "",
                       "accessPolicy":  "@{href=https://dev.okta.com/api/v1/policies/rst3d2b42oy6MyKnm697}",
                       "users":  "@{href=https://dev.okta.com/api/v1/apps/0oa167/users}",
                       "deactivate":  "@{href=https://dev.okta.com/api/v1/apps/0oa167/lifecycle/deactivate}"
                   }
    },
    {
        "id":  "0oa9me",
        "orn":  "orn:okta:idp:00o38o:apps:office365:0oa9me",
        "name":  "office365",
        "label":  "Office 365",
        "status":  "ACTIVE",
        "lastUpdated":  {
                            "value":  "\/Date(1741737338000)\/",
                            "DisplayHint":  2,
                            "DateTime":  "Tuesday, March 11, 2025 4:55:38 PM"
                        },
        "created":  {
                        "value":  "\/Date(1701388060000)\/",
                        "DisplayHint":  2,
                        "DateTime":  "Thursday, November 30, 2023 3:47:40 PM"
                    },
        "accessibility":  {
                              "selfService":  false,
                              "errorRedirectUrl":  null,
                              "loginRedirectUrl":  null
                          },
        "licensing":  {
                          "seatCount":  25
                      },
        "visibility":  {
                           "autoLaunch":  false,
                           "autoSubmitToolbar":  true,
                           "hide":  "@{iOS=False; web=False}",
                           "appLinks":  "@{calendar=True; mail=True; teams=False; sites=False; delve=False; sway=False; login=True; powerbi=False; people=True; excel=False; yammer=False; newsfeed=False; onedrive=True; word=False; planner=False; powerpoint=False; tasks=False; forms=False; crm=False}"
                       },
        "features":  [

                     ],
        "signOnMode":  "BROWSER_PLUGIN",
        "credentials":  {
                            "scheme":  "EXTERNAL_PASSWORD_SYNC",
                            "userNameTemplate":  "@{template=user.login}",
                            "revealPassword":  false,
                            "signing":  ""
                        },
        "settings":  {
                         "app":  "",
                         "notifications":  "@{vpn=}",
                         "manualProvisioning":  false,
                         "implicitAssignment":  false,
                         "notes":  "@{admin=; enduser=}"
                     },
        "_links":  {
                       "uploadLogo":  "@{href=https://dev.okta.com/api/v1/apps/0oa9me/logo; hints=}",
                       "appLinks":  "    ",
                       "profileEnrollment":  "@{href=https://dev.okta.com/api/v1/policies/rst3d2b42qqKIGg87697}",
                       "policies":  "@{href=https://dev.okta.com/api/v1/apps/0oa9me/policies; hints=}",
                       "groups":  "@{href=https://dev.okta.com/api/v1/apps/0oa9me/groups}",
                       "logo":  "",
                       "accessPolicy":  "@{href=https://dev.okta.com/api/v1/policies/rst9mehbr9XZJbbRE697}",
                       "users":  "@{href=https://dev.okta.com/api/v1/apps/0oa9me/users}",
                       "deactivate":  "@{href=https://dev.okta.com/api/v1/apps/0oa9me/lifecycle/deactivate}"
                   }
    },
    {
        "id":  "0oa9mt",
        "orn":  "orn:okta:idp:00o38o2bcJ1wpYAaG696:apps:amazon_aws:0oa9mt",
        "name":  "amazon_aws",
        "label":  "AWS",
        "status":  "ACTIVE",
        "lastUpdated":  {
                            "value":  "\/Date(1701837468000)\/",
                            "DisplayHint":  2,
                            "DateTime":  "Tuesday, December 5, 2023 8:37:48 PM"
                        },
        "created":  {
                        "value":  "\/Date(1701441168000)\/",
                        "DisplayHint":  2,
                        "DateTime":  "Friday, December 1, 2023 6:32:48 AM"
                    },
        "accessibility":  {
                              "selfService":  false,
                              "errorRedirectUrl":  null,
                              "loginRedirectUrl":  null
                          },
        "visibility":  {
                           "autoLaunch":  false,
                           "autoSubmitToolbar":  false,
                           "hide":  "@{iOS=False; web=False}",
                           "appLinks":  "@{login=True}"
                       },
        "features":  [

                     ],
        "signOnMode":  "SAML_2_0",
        "credentials":  {
                            "userNameTemplate":  "@{template=user.login; type=CUSTOM; pushStatus=PUSH}",
                            "signing":  "@{kid=5n5XCB}"
                        },
        "settings":  {
                         "app":  "",
                         "notifications":  "@{vpn=}",
                         "manualProvisioning":  false,
                         "implicitAssignment":  false,
                         "notes":  "@{admin=notes; enduser=}",
                         "signOn":  "@{defaultRelayState=; ssoAcsUrl=https://signin.aws.amazon.com/saml; audienceOverride=; recipientOverride=; destinationOverride=; attributeStatements=System.Object[]}"
                     },
        "_links":  {
                       "help":  "@{href=https://dev.okta.com/app/amazon_aws/0oa9mt/setup/help/SAML_2_0/external-doc; type=text/html}",
                       "metadata":  "@{href=https://dev.okta.com/api/v1/apps/0oa9mt/sso/saml/metadata; type=application/xml}",
                       "uploadLogo":  "@{href=https://dev.okta.com/api/v1/apps/0oa9mt/logo; hints=}",
                       "appLinks":  "",
                       "profileEnrollment":  "@{href=https://dev.okta.com/api/v1/policies/rst3d2b42qqKIGg87697}",
                       "policies":  "@{href=https://dev.okta.com/api/v1/apps/0oa9mt/policies; hints=}",
                       "groups":  "@{href=https://dev.okta.com/api/v1/apps/0oa9mt/groups}",
                       "logo":  "",
                       "accessPolicy":  "@{href=https://dev.okta.com/api/v1/policies/rst3d2b42mLDP4NuV697}",
                       "users":  "@{href=https://dev.okta.com/api/v1/apps/0oa9mt/users}",
                       "deactivate":  "@{href=https://dev.okta.com/api/v1/apps/0oa9mt/lifecycle/deactivate}"
                   }
    },
    {
        "id":  "0oaami",
        "orn":  "orn:okta:idp:00o38o:apps:bookmark:0oaami",
        "name":  "bookmark",
        "label":  "Chronicle - SIEM",
        "status":  "ACTIVE",
        "lastUpdated":  {
                            "value":  "\/Date(1706819746000)\/",
                            "DisplayHint":  2,
                            "DateTime":  "Thursday, February 1, 2024 12:35:46 PM"
                        },
        "created":  {
                        "value":  "\/Date(1704906745000)\/",
                        "DisplayHint":  2,
                        "DateTime":  "Wednesday, January 10, 2024 9:12:25 AM"
                    },
        "accessibility":  {
                              "selfService":  false,
                              "errorRedirectUrl":  null,
                              "loginRedirectUrl":  null
                          },
        "visibility":  {
                           "autoLaunch":  false,
                           "autoSubmitToolbar":  false,
                           "hide":  "@{iOS=False; web=False}",
                           "appLinks":  "@{login=True}"
                       },
        "features":  [

                     ],
        "signOnMode":  "BOOKMARK",
        "credentials":  {
                            "userNameTemplate":  "@{template=${source.login}; type=BUILT_IN}",
                            "signing":  ""
                        },
        "settings":  {
                         "app":  "@{requestIntegration=False; url=https://app.backstory.chronicle.security/}",
                         "notifications":  "@{vpn=}",
                         "manualProvisioning":  false,
                         "implicitAssignment":  false,
                         "notes":  "@{admin=; enduser=}"
                     },
        "_links":  {
                       "uploadLogo":  "@{href=https://dev.okta.com/api/v1/apps/0oaami/logo; hints=}",
                       "appLinks":  "",
                       "profileEnrollment":  "@{href=https://dev.okta.com/api/v1/policies/rst3d2b42qqKIGg87697}",
                       "policies":  "@{href=https://dev.okta.com/api/v1/apps/0oaami/policies; hints=}",
                       "groups":  "@{href=https://dev.okta.com/api/v1/apps/0oaami/groups}",
                       "logo":  "",
                       "accessPolicy":  "@{href=https://dev.okta.com/api/v1/policies/rst3d2b42mLDP4NuV697}",
                       "users":  "@{href=https://dev.okta.com/api/v1/apps/0oaami/users}",
                       "deactivate":  "@{href=https://dev.okta.com/api/v1/apps/0oaami/lifecycle/deactivate}"
                   }
    }
]

I am trying to get to an output that looks like this:

id,label,ssoAcsUrl
0oa9mt,AWS,https://signin.aws.amazon.com/saml

The objective of Stack Overflow is not to fix AI hallucinations. Note also that all AI generated content is banned in Stackoverflow. Instead remove the invalid script from the question, try to explain what is that you are trying to do, attempt to resolve yourself and share what didn't work. Don't forget to add example inputs, expected outputs and actual outputs. Reading How to Ask and minimal reproducible example are good guidance on how to write correctly a question. — aled
– aled, Commented Mar 26 at 16:55
Thank you, I've updated the source post with the input data, and desired output data. — Jordan M
– Jordan M, Commented Mar 26 at 19:20
The first part of the explanation is still unclear. Also please remove the AI content from the question. — aled
– aled, Commented Mar 27 at 23:56

aled · Accepted Answer · 2025-04-02 11:48:04Z

Filtering items from the input array and getting the id and label from the input is pretty direct. The complexity is getting the URL from the string in settings.signOn. The value is not compatible with any DataWeave built-in format. A kind of a hack is to first extract the substring of key values separated by ;. I used a regular expression for that. Then I replace the ; by a newline character \n. Then I read the result as it were a Java properties files which is supported by DataWeave. At that point it will return an object which we can access by the key of the URL.

%dw 2.0
output application/csv

fun getInsideProperties(s: String)=
 (s scan (/@\{([^}]+)}/))[0][1] splitBy ";" joinBy "\n"

fun getUrl(s: String)=
    read(getInsideProperties(s), "text/x-java-properties")["ssoAcsUrl"]
---
payload 
    filter ($.signOnMode == "SAML_2_0")
    map {
            id: $.id,
            label: $.label,
            ssoAcsUrl: getUrl($.settings.signOn)
    }

Output:

id,label,ssoAcsUrl
0oa9mt,AWS,https://signin.aws.amazon.com/saml

Collectives™ on Stack Overflow

JSON to CSV via MuleSoft DataWeave - Okta App data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related