How to pass a JSON value in a JSON string as a parameter
I have a workflow that produces a JSON string that needs to be submitted to a server workflow, then passed to an INSERT statement in a SQL Non-Query activity as a parameter. The goal is get the JSON string put into a JSON column in the database.
I seem to be caught in a vortex of "escaped" JSON versus "unescaped" JSON battling between the workflow parameters and the SQL Non-Query parameter.
If I put the value in double-quotes like a normal string, it fails during the PARSE JSON activity that I use to submit parameters to the server workflow.
If I send the JSON value as JSON, it fails at the database because the server workflow sends "escaped" JSON to the database which the database rejects as invalid JSON.
What is the secret sauce to put on my JSON so I can get it inserted into a JSON column at the database?
Attached is the workflow EditCRORganization that calls the server workflow EditTableValues
-
Use a backtick
=`[ { "en": { "panel.title": "Save File_mod", "panel.message": "Enter the File Name_mod", "panel.button.save": "Save_mod", "panel.button.cancel": "Cancel_mod" } }, { "es": { "panel.title": "Es Bueno!", "panel.message": "Introduzca un nombre de fichero", "panel.button.save": "Guardar", "panel.button.cancel": "Cancelar" } } ]`0 -
Thanks for the quick response.
The variable $submissionJson.result is a template literal:
=$submissionJson.result = `{"community_registry":{"submission":{
"an_submission_stg_id": ${$stagingId.result},
"an_staging_status_id": 101,
"ac_cr_log": "",
"av_audit_user": "${$auditUser.result}"
},${$organizationJson.result},${$contactJson.result},${$contactPhoneJson.result},${$orgContactJson.result}}}`
I pass the workflow parameters in a template literal:
= `{
"ApplicationName":"CRSubmission",
"DatabaseConnection":"${$databaseConnection.result}",
"StagingId":${$stagingId.result},
"SubmissionJson":"${$submissionJson.result}",
"AuditUser":"${$auditUser.result}"
}`
This fails the PARSE JSON activity trying to send the workflow parameters:
"{
"ApplicationName":"CRSubmission",
"DatabaseConnection":"GISMAINTDEV-CommunityRegistry",
"StagingId":22330,
"SubmissionJson":"{"community_registry":{"submission":{
"an_submission_stg_id": 22330,
"an_staging_status_id": 101,
"ac_cr_log": "",
"av_audit_user": "rocky.racoon@saloon.com"
},"organization":{
"an_staging_status_id": 101,
"an_organization_stg_id": 22334,
"an_submission_stg_id": 22330,
"an_organization_id": 22329,
"av_organization_name": "Saloons Coalition",
"an_org_category_id": 102,
"av_org_category_other": "",
"av_organization_email": "",
"av_organization_website": "",
"an_number_of_households": 0,
"an_meeting_frequency_id": 103,
"av_audit_user": "rocky.racoon@saloon.com"
},"contacts":[
{
"an_staging_status_id": 101,
"an_contact_stg_id": 22331,
"an_submission_stg_id": 22330,
"an_contact_id": 22332,
"an_title_id": 100,
"av_first_name": "Rockwell",
"av_middle_name": "",
"av_last_name": "Racoon",
"an_suffix_id": 101,
"av_preferred_name": "Rocky",
"av_mailing_address": "",
"av_city": "",
"av_state": "TX",
"av_zipcode": "",
"av_email": "rocky.racoon@saloon.com",
"av_audit_user": "rocky.racoon@saloon.com"
}],"phones":[
{
"an_staging_status_id": 101,
"an_contact_phone_stg_id": 22331,
"an_submission_stg_id": 22330,
"an_contact_phone_id": 22331,
"an_contact_id": 22332,
"an_phone_type_id": 102,
"av_contact_phone": 5124487577,
"av_audit_user": "rocky.racoon@saloon.com"
}],"organization_contact":[{
"an_staging_status_id": 101,
"an_organization_contact_stg_id": 22331,
"an_submission_stg_id": 22330,
"an_organization_contact_id": 22333,
"an_organization_id": 22329,
"an_contact_id": 22332,
"an_org_contact_type_id": 103,
"av_office_held": "Submitter",
"av_audit_user": "rocky.racoon@saloon.com"
},{
"an_staging_status_id": 101,
"an_organization_contact_stg_id": 22331,
"an_submission_stg_id": 22330,
"an_organization_contact_id": 22333,
"an_organization_id": 22329,
"an_contact_id": 22332,
"an_org_contact_type_id": 101,
"av_office_held": "Slinger",
"av_audit_user": "rocky.racoon@saloon.com"
}]}}",
"AuditUser":"rocky.racoon@saloon.com"
}"
I assume because of the leading and trailing double-quotes?
If I remove the double-quotes from the workflow parameter JSON, the JSON sends to the server workflow as an Object. If I try to CONVERT to JSON activity that result, it puts escape characters throughout the resulting JSON string which fails to INSERT at the database as invalid JSON.
Where should I define the "new" template literal with tick marks for just the ${$submissionJson.result}?
0 -
Yes, its confusing. The above is for statically stored JSON. Here is example reading from a file:
- Send Web Request
- URL=$urlSiteResources.value+'Config.json'
- Parse JSON
- JSON=$workflowConfigsRaw.text
- Run Workflow (to generate a token)
- Arguments={config: $config.result}
- Generate ArcGIS Token
- Generate Token URL=$getWorkflowInputs.inputs.generatorUrl
- Expiration=$getWorkflowInputs.inputs.generatorUrl || 60
Hope that helps
0 - Send Web Request
-
Um. Not sure. I'm not doing anything with files or ArcGIS. I'm just collecting user input in display forms and building a JSON string on the fly as I go. Then I'd like to insert that string into a JSON column in the database. I just can't seem to get the JSON string to be "just a string" to insert.
1 -
Are you using the Parse JSON activity? Once you do that you can pass the text of it's result.
0 -
Sean McClurkan - did you ever find out how to beat the escape characters for assembling a json string?
0
Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.
Kommentare
6 Kommentare