xml and json Transformaion

[amit_mohanty88]

I want to achieve below functionality in Workato without Ruby / Java Script / Python Scripting as scripts are not allowed by our customer .Also JSON Transformations by Workato Action not available in my plan .Without these is there a way to acheive below ?

1) Sort a input json alphabetically ascedending

2) Remove any specific fields from a input json and return updated json

3)Convert any given input  json to xml and any given input  xml to json 

4) Compare 2 json elment structure and return any field mismatch between the 2 json which is present in 1 json not the other json

[amit_mohanty88]

Hi @rajeshjanapati  @shivakumara ,

Thank you for your suggestion. 1 query i got like in step 2 and step 9 parse json doument for that we need to give sample json . But this recipe should be able to process any json not only any specific json so what can we give sample json in parse json  doument  in step 2 and step 9 ?

Regards,

Amit Mohanty

[rajeshjanapati]

Hi @amit_mohanty88,

It can be possible with any type of json, with respect to that we need to provide the sample json in the Parse Json step.

Please find the below sample json data which i have used in step_2 and step_9 and output from step_11 and query:
step_2 json:

{
  "employees": [
    {"firstname": "John", "lastname": "Doe", "city": "San Jose", "state": "CA"},
    {"firstname": "Apple", "lastname": "fruit", "city": "1", "state": "def@gmail.com"},
    {"firstname": "Monkey", "lastname": "animal",  "city": "2", "state": "ghi@gmail.com"}
  ]
}

 Step_9 json:

{
  "employees": [
    {"firstname": "John", "lastname": "Doe", "city": "San Jose", "state": "CA"}
    ]
}

Step_11 output:

{
	"rows": [
		{
			"firstname": "Apple",
			"lastname": "fruit",
			"city": "1",
			"state": "def@gmail.com"
		},
		{
			"firstname": "Monkey",
			"lastname": "animal",
			"city": "2",
			"state": "ghi@gmail.com"
		}
	]
}

Query:

Thanks & Regards,

J Rajesh Kumar.

[amit_mohanty88]

Hi @rajeshjanapati ,

What if to the recipe we got input json as below . Here in your step 2 & step 9 you expect json with employee as input .So the parse json document wont be able to parse the below json right with your above sample json document ?

{

    "glossary": {

        "title": "example glossary",

"GlossDiv": {

            "title": "S",

"GlossList": {

                "GlossEntry": {

                    "ID": "SGML",

"SortAs": "SGML",

"GlossTerm": "Standard Generalized Markup Language",

"Acronym": "SGML",

"Abbrev": "ISO 8879:1986",

"GlossDef": {

                        "para": "A meta-markup language, used to create markup languages such as DocBook.",

"GlossSeeAlso": ["GML", "XML"]

                    },

"GlossSee": "markup"

                }

            }

        }

    }

}

[rajeshjanapati]

@amit_mohanty88, Based on my analysis and understanding, if you want to execute a query in Workato, the input must be in a table-like structure in a collection.

Suggestions:

1. My suggestion is that for scenarios where native Workato actions cannot handle the requirement, we should leverage scripting, that’s exactly why Workato introduced this capability. You can also communicate to the customer or business team that this particular scenario cannot be implemented without scripting, and the script involved is minimal, simple, and easy to understand, especially when it comes to data comparison.

2. If the team is still not comfortable using scripting (Python), an alternative approach would be to convert the JSON into CSV  or send the data in CSV File, can read from any source system or store each required JSON field into a data table. Then, using SQL Transformations by Workato, you can easily compare both CSV files (file_1 and file_2) through a single query step.

In my view, it’s best to communicate this clearly to the team and proceed with the scripting approach, as it’s the simplest and most efficient way to handle this scenario.

Hope you got my point?

@shivakumara, If possible, will you please add any suggestions to the problem?

[shivakumara]

Hi @amit_mohanty88 ,

Here is my answer for the asked question:
the Parse JSON action cannot parse the given JSON without a defined schema?
Answer: The sample document is essential because it defines the schema structure required to parse incoming data. The sample data space is used to establish this schema, which will then be considered when forming the output for future steps.

Please ensure you have clarity on the schema structure from the requirements. Once that is confirmed, any one of us can assist you further.

Thanks and Regards,
Shivakumara K A