Reading JSON

Alteryx Designer can read and parse JSON in structured form through the Input Data tool (as of Designer version 2024.1). At this time, the AMP engine supports only one top level of nested objects.

Example JSON Input

[
    {
        "business_id": "vcNAWiLM4dR7D2nwwJ7nCA",
        "open": true,
        "review_count": 9,
        "hours": {
            "Tuesday": {
                "close": "17:00",
                "open": "08:00"
            },
            "Friday": {
                "close": "17:00",
                "open": "08:00"
            },
            "Monday": {
                "close": "17:00",
                "open": "08:00"
            },
            "Wednesday": {
                "close": "17:00",
                "open": "08:00"
            },
            "Thursday": {
                "close": "17:00",
                "open": "08:00"
            }
        },
        "stars": 3.5
    },
    {
        "business_id": "UsFtqoBl7naz8AVUBZMjQQ",
        "open": true,
        "review_count": 4,
        "hours": {
            "Monday": {
                "close": "21:00",
                "open": "10:00"
            },
            "Tuesday": {
                "close": "21:00",
                "open": "10:00"
            },
            "Friday": {
                "close": "21:00",
                "open": "10:00"
            },
            "Wednesday": {
                "close": "21:00",
                "open": "10:00"
            },
            "Thursday": {
                "close": "21:00",
                "open": "10:00"
            },
            "Sunday": {
                "close": "18:00",
                "open": "11:00"
            },
            "Saturday": {
                "close": "21:00",
                "open": "10:00"
            }
        },
        "stars": 3.5
    },
    {
        "business_id": "cE27W9VPgO88Qxe4ol6y_g",
        "open": false,
        "review_count": 5,
        "stars": 2.5
    }
]

Default Settings

Reading Data in Unstructured Form

By default, the Input Data tool reads data in an unstructured form. In the Input Data tool options...

The AMP Only: Read Data in structured form checkbox is unchecked by default.
The Unstructured mode only: Parse Value as String checkbox is checked by default.

Values are output into a single string field. Two fields are output, "JSON Name" and "JSON_ValueString". Referring to the input example above, the following data outputs from the tool:

JSON_Name	JSON_ValueString
0.business_id	vcNAWiLM4dR7D2nwwJ7nCA
0.open	1
0.review_count	9
0.hours.Tuesday.close	17:00
0.hours.Tuesday.open	08:00
0.hours.Friday.close	17:00
0.hours.Friday.open	08:00
0.hours.Monday.close	17:00
0.hours.Monday.open	08:00
0.hours.Wednesday.close	17:00
0.hours.Wednesday.open	08:00
0.hours.Thursday.close	17:00
0.hours.Thursday.open	08:00
0.stars	3.5
1.business_id	UsFtqoBl7naz8AVUBZMjQQ
1.open	1
1.review_count	4
1.hours.Monday.close	21:00
1.hours.Monday.open	10:00
1.hours.Tuesday.close	21:00
1.hours.Tuesday.open	10:00
1.hours.Friday.close	21:00
1.hours.Friday.open	10:00
1.hours.Wednesday.close	21:00
1.hours.Wednesday.open	10:00
1.hours.Thursday.close	21:00
1.hours.Thursday.open	10:00
1.hours.Sunday.close	21:00
1.hours.Sunday.open	10:00
1.hours.Saturday.close	21:00
1.hours.Saturday.open	10:00
1.stars	3.5
2.business_id	cE27W9VPgO88Qxe4ol6y_g
2.open	0
2.review_count	5
2.stars	2.5

Metadata

Name	Type	Size	Source	Description
JSON_Name	V_WString	1073741823	JSON_Input
JSON_ValueString	V_WString	1073741823	JSON_Input

Reading Data in Structured Form

The AMP Only: Read Data in structured form checkbox is checked.
Unstructured mode only: Parse Value as String: Regardless of whether the checkbox is checked or unchecked, this setting only applies to unstructured reading.

Values are output into multiple files based on the top-level of JSON objects with appropriate data types:

business_id	open	review_count	hours	stars
vcNAWiLM4dR7D2nwwJ7nCA	TRUE	9	`{"Tuesday":{"close":"17:00","open":"08:00"},"Friday":{"close":"17:00","open":"08:00"},"Monday":{"close":"17:00","open":"08:00"},"Wednesday":{"close":"17:00","open":"08:00"},"Thursday":{"close":"17:00","open":"08:00"}}`	3.5
UsFtqoBl7naz8AVUBZMjQQ	TRUE	4	`{"Monday":{"close":"21:00","open":"10:00"},"Tuesday":{"close":"21:00","open":"10:00"},"Friday":{"close":"21:00","open":"10:00"},"Wednesday":{"close":"21:00","open":"10:00"},"Thursday":{"close":"21:00","open":"10:00"},"Sunday":{"close":"18:00","open":"11...`	3.5
cE27W9VPgO88Qxe4ol6y_g	FALSE	5		2.5