Output to JSON

hellyars

Hi,

I see you can now, or maybe you always could, output to JSONL

My Output Data tool has File Format set to JSONL and Code Page set to UTF-8.

I keep getting a Missing Root error.

I only have two fields ID, and Text.

Does the Output Data tool automatically convert to JSONL or do I need to do anything?

Output

Help

Accepted answers

All comments

aatalai

@hellyars have you tried the JSON build tool?

Verakso

Doesn't really matter how you do it, but JSON arrays needs to have a root. especially the JSON build tool.
Prefix your columns with data.

2024-06-07 12_59_25-Alteryx Designer x64 - New Workflow3_.png

EDIT: Forgot to mention, if you for some reason do NOT need the root, you can remove it by a string replace before saving the data

Br (ツ)

Thomas

json.yxmd

hellyars

@aatalai @Verakso

No, I did not try the JSON build tools.
Previously, I just exported to CSV and ran a python script to convert direct to JSONL

Next, I set the Output Data tool to export to JSON then ran a modify version of my python script to convert to JSONL.

It worked and there was no need to use the JSON build tools prior to the Output Data Tool.

If the JSON Build Tool was not necessary to export a simple JSON file previously, why is it necessary now.
And yes, I am poly-sci guy not a coder.

apathetichell

@hellyars I think you can do what you want with a standard set of tools like:

recordid (or tile or some grouping logic)

transpose

formula - to create key/value pairs

summarize (in group by concat mode)

formula (to add the beginning { and } if needed)

formula tool to create filepath.

output data (set for .csv with /0 as a delimiter/replace entire filepath - with your naming convention created above.

json/jsonl is plain text. you just need to format your text correctly.

hellyars

I think we might be talking past each other.

If I do not use the JSON Build tool. The Output Data Tool will output the example below when set to JSON (w/o errors).

[
    {
        "FOO_YEAR": "2025",
        "FOO_ACCT": "6441D",
        "FOO_ACTIVITY": "005",
        "FOO_TITLE": "BMW 1250 GSA",
        "FOO_OBJECT_ID": "B-61-01-C-1250-GSA-MC",
        "FOOD_TXT": "BLAH BLAH BLAH"
    }
]

I can successfully take the JSON from the Output Data Tool and use a python script (via import json) to convert to JSONL. Which means all it does it get rid of the [ ] and the returns between fields/values.

So, why do I get ROOT ELEMENT MISSING when I set the Output Tool to JSONL?

Verakso

Hi @hellyars
Unfortunately my designer is probably to old, so I can't choose JSONL as format in my output tool, and JSONL is missing from the list of supported formats, when I look in the current documentation
https://help.alteryx.com/current/en/designer/data-sources.html#idp378082

If I had to guess, it think you get the ROOT ELEMENT MISSING, because Alteryx still treats JSONL as JSON

My suggestion is, you build your JSONL as a "normal" JSON array, and then strip the start [ and end ], and save the file as a text file, also suggested by @apathetichell

BTW, when using python, are you calling a python script from Alteryx using the Command Line tool, or are you using the built-in python tool - just curious

Br (ツ)

Thomas

hellyars

@Verakso

Using Altyeryx for data prep.
Working with a python library that requires JSONL inputs for processing.

New to working with python libraries directly form CLI.
But processing can be significantly faster using CLI as you can access full system resources and GPU.

Seems Alteryx limits you to only 8 threads vs. full 32 threads from CLI and/or off-load to GPU.

Saves minutes.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703