parsing JSON

[CarCrazyAl]

I am brand new to Alteryx and am trying to prove out a particular Use Case.  I want to Source a JSON file, which includes a couple of nested objects, and ultimately store the data in tabular form in an Azure SQL DB.

Using an Input Data component, I connect to a sample JSON file.  However, I don't know what to do next!  I connect a JSON Parse component, which sort of seems to work.  The JSON_Name Input flag shows my data, but everything is prefixed with "0.", meaning I see 0.id, 0.key, etc.  

Running the Workflow returns 2 error messages on every field:
    Invalid value at character position: 0

    The document root must not be followed by other values, at character position: 1

I have attached the .json file I am working with and hope someone can help me figure this out.

Thank you!

[LordNeilLord · Accepted solution]

Hey @CarCrazyAl

The number you see is a record grouping which will allow you to turn the data back into a tabular format.

I have attached an example for you

[CarCrazyAl]

@LordNeilLord - thank you very much for your response!  You put that Workflow together VERY QUICKLY!  At this point I do not completely understand what you did & how you broke it all down, but will review and work through your solution.

Thanks again!

[LordNeilLord]

Hey no worries!

Just ask if you have any questions :)

Neil

[Ddee]

Thanks @LordNeilLord - this is the only place I've seen the need to use Summarize before the JSON Parse tool to get it all on one line!

Easy once you know, frustrating if you don't!

[GreggD]

What does the summarize tool do and how do you configure it to properly feed the Json Parse tool?

[Ddee]

Reading in the JSON as CSV (with delimiter as '\0'), the JSON Parse tool needs all the line breaks removed, which you can do with the Concat action in the Summarize tool.

[GreggD]

Thanks for the explanation. Being new to Alteryx and having been away from actively coding for the past 15 years has left me with the challenge of having to learn how to work with APIs, understand the JSON format and how to leverage the many tools in Alteryx to parse our particularly complex JSON output. The output from JIRA appears to be more complex than the simple examples that I have seen in the Alteryx community. I think what people like me could really use as a tutorial on how JSON files are structured and what you need to do (process) to parse a JSON file to convert it into a format (like a CSV) that can be consumed by traditional tools. So the tutorial would show the steps in the parsing process. Then follow that with how to use the tools in Alteryx to accomplish each step in the process. Start with a simple JSON file and then show how to handle more complex examples. At the end the student would have a really solid understanding of JSON and also how to use a lot of Alteryx tools to process structured text files into tabular data that a database can consume, as well as do the reverse. Pull data from a database and create a JSON file. That would be a very powerful capability to have, given the rise of micro services and the use of APIs in corporate architectures. I hope the Alteryx support team reads this.... insert smiley face emoticon here.