JSON - list of files [SOLVED]

[magnussandstrom]

Hi, I'm using HTTP request to get a list of files as a JSON response. I then use JSON pickup to pickup the response as a JSON dataset. So far so good.

Now I would like to download the files listed in the JSON response using HTTP request. But this part I'm struggling with. If it's only one file in the response there is no problem, but how should I handle several files?

This is how the JSON looks like:

Code: Select all

{"ls": [
{"filename": "test1.pdf", "size": 1867179, "can_inbox": true}, 
{"filename": "test2.pdf", "size": 1867179, "can_inbox": true}
{"filename": "test3.pdf", "size": 1867179, "can_inbox": true}
]}

[laurentd]

Hi Magnus,

You have to loop in the JSON info to get the name of eacht file to download (and preferably the url or full path).

To do this, you can configure JSON pickup to pickup the incoming JSON as an XML dataset.
Then you can use the Variable XPath Repeater app to loop in the XML dataset, duplicate the job n times, and define the right private data, with the name/url/path of each job, and possibly other job metadata.

Then you will be able to dowload it with HTTP request, or inject it if it is waiting in a job repository.

[JimmyHartington]

I do not know which platform you are on.
But using the jq command line tool it is possible to manipulate JSON. I use chocolatey on Windows and Homebrew on macOS to install it.

With the help of ChatGPT I was able to create this bash script which works on macOS.

Code: Select all

jq -c '.ls[]' $1 | while read i; do
name=$(echo $i | jq -r '.filename')
echo $i > "$(pwd)/$name.json"
done

I execute it with

Code: Select all

./filename.sh JSONfile.json

So you can probably use it with the Run Command app.

This is the output of one of the 3 files from you sample data:

Code: Select all

{"filename":"test1.pdf","size":1867179,"can_inbox":true}

[JimmyHartington]

And here is a .cmd script for Windows. The one argument for the script should be the input json-file.

Code: Select all

@echo off
for /f %%i in ('C:\ProgramData\chocolatey\lib\jq\tools\jq.exe -c ".ls[]" %1') do (
    for /f "delims=" %%f in ('echo %%i ^| C:\ProgramData\chocolatey\lib\jq\tools\jq.exe -r ".filename"') do (
        echo %%i > %%~nf.json
    )
)

[JimmyHartington]

And here is the info when one of the output files has been processed with the JSON Pickup.

[magnussandstrom]

Thanks Laurent and Jimmy for two creative solutions!

I solved it this way:

1. Pickup JSON as Legacy app XML
2. Export meta data as XML file
3. Saxon with a simple XSL to split the XML (one per filename)
4. XML pickup
5. HTTP request

[JimmyHartington]

As always there are many ways to achieve the same result

Would you mind sharing how you split the XML with Saxon?

[magnussandstrom]

Nowadays I use ChatGPT-4 to create varios XSLT's. In this case my prompt looked like this:

Can you please write me an XSLT code that I can use to split my XML in one XML file per array item, the file name of the outgoing XML files should be named with arrays item name, in this case 0.xml and 1.xml.

This is my incoming XML:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<JSON>
  <ls>
    <array item="0">
      <filename type="string">test.pdf</filename>
      <size type="number">1867179</size>
      <modtime type="number">1713856059</modtime>
    </array>
    <array item="1">
      <filename type="string">test2.pdf</filename>
      <size type="number">1867179</size>
      <modtime type="number">1713856568</modtime>
    </array>
  </ls>
</JSON>

Response from ChatGPT:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:fn="http://www.w3.org/2005/xpath-functions"
    exclude-result-prefixes="xs fn">

    <xsl:output method="xml" indent="yes"/>
    <xsl:strip-space elements="*"/>

    <!-- Identity template: copies everything as is -->
    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

    <!-- Template for root element -->
    <xsl:template match="/JSON">
        <!-- Apply templates to each array element -->
        <xsl:apply-templates select="ls/array"/>
    </xsl:template>

    <!-- Template to process each array element -->
    <xsl:template match="ls/array">
        <!-- Define output file based on the item attribute -->
        <xsl:result-document method="xml" href="{@item}.xml" indent="yes">
            <!-- Create the root element -->
            <array>
                <!-- Copy all the child nodes of the array -->
                <xsl:apply-templates/>
            </array>
        </xsl:result-document>
    </xsl:template>
</xsl:stylesheet>

[JimmyHartington]

Ok.

And how do you process the XML with the XLST using Saxon?

[magnussandstrom]

Oh sorry!

First I installed Saxon 9HE using Chocolatey (choco install saxonhe). Then I installed the Saxonica Saxon Switch app.

In the Saxon app element you just need to choose the xsl-file that you want to use for transforming the incoming XML.

flow_saxon_settings.png (140.58 KiB) Viewed 360 times

[magnussandstrom]

Ok.

And how do you process the XML with the XLST using Saxon?

I hope that helps Jimmy? Have you been working with XSLT in Switch? And if so how?

[JimmyHartington]

This helps in understanding how.

I have not worked with XLSTs in Switch before. It seems a bit daunting to write XLST, but maybe with the help of AI and testing it could work.

Some of my flows could surely be optimized in some cases by using XLSTs instead of some of my workarounds.

[magnussandstrom]

This helps in understanding how.

I have not worked with XLSTs in Switch before. It seems a bit daunting to write XLST, but maybe with the help of AI and testing it could work.

Some of my flows could surely be optimized in some cases by using XLSTs instead of some of my workarounds.

XSLT is very powerful IMHO. I use it for alot of different stuff in my Switch flows. I couldn't live without it!

[JimmyHartington]

XSLT is very powerful IMHO. I use it for alot of different stuff in my Switch flows. I couldn't live without it!

I just tested with one of my flows and got it to work.
Like you with a xslt created with the help of ChatGPT.

[magnussandstrom]

I just tested with one of my flows and got it to work.
Like you with a xslt created with the help of ChatGPT.

Great to hear Jimmy! Another nice thing with using Saxon in Switch is that you get very useful information in Messages if anything fails.