How to convert imported CSV output JSON into a different JSON structure

[mgaussie]

Hey all,

I have a CSV file that has 3 columns, first_name, last_name, company - that when I import and output, this outputs the data correctly:

The JSON query I need to send via API is:

{
    "requests": [
        {
            "params": {
                "first_name": "john",
                "last_name": "smith",
                "company": "company name"
            }
        },
        {
            "params": {
                "first_name": "mary",
                "last_name": "jane",
                "company": "company enterprise"
            }
        }
    ]
}

I’ve tried using a Set Value and defining the appropriate schema, but regardless of what I do I still get the same output that the initial CSV import ‘output’ generates, I can’t find a way to input the “params” as an object and / or restructure the schema to correctly query an API with the JSON.

It feels like I’m missing something simple here, any help appreciated!

[sid]

Tried testing this out, and was able to create a JSON string as required. But, the output of this in browser is a string, not a JSON. Not sure how it would get treated when you pass this to API.

 {
        "name": "req",
        "module": "core",
        "action": "setvalue",
        "options": {
          "key": "req",
          "value": [
            {
              "first_name": "john",
              "last_name": "smith",
              "company": "company name"
            },
            {
              "first_name": "mary",
              "last_name": "jane",
              "company": "company enterprise"
            }
          ]
        },
        "outputType": "object",
        "output": true
      },
      {
        "name": "requests",
        "module": "core",
        "action": "setvalue",
        "options": {
          "key": "requests",
          "value": "["
        },
        "output": true
      },
      {
        "name": "repeat1",
        "module": "core",
        "action": "repeat",
        "options": {
          "repeat": "{{req}}",
          "exec": {
            "steps": {
              "name": "requests",
              "module": "core",
              "action": "setvalue",
              "options": {
                "key": "requests",
                "value": "{{requests}} \"params\":{ \"first_name\": \"{{first_name}}\", \"last_name\":\"{{last_name}}\", \"company\":\"{{company}}\"},"
              },
              "output": true
            }
          }
        },
        "output": true,
        "meta": [
          {
            "name": "$index",
            "type": "number"
          },
          {
            "name": "$number",
            "type": "number"
          },
          {
            "name": "$name",
            "type": "text"
          },
          {
            "name": "$value",
            "type": "object"
          }
        ],
        "outputType": "array"
      },
      {
        "name": "requests",
        "module": "core",
        "action": "setvalue",
        "options": {
          "key": "requests",
          "value": "{{requests.substr(0, (requests.length() - 1))}}]"
        },
        "output": true,
        "outputType": "object"
      }

Try adding this steps to your server action. Modify as per your setup.

[mgaussie]

Thank you for this @sid - i tried creating a new Server Action and copy / paste this into the the editor - but it returns an error.

Would you mind perhaps taking a screen shot of the server action steps instead?

[bpj]

If the output is a string, you could create a custom formatted to convert it. This post included it

How to : Pass an Array to a Server Action to Write to the Database

Step 1 : Create server side custom formatter <?php namespace lib\core; function formatter_decode($val){ return json_decode($val,true); } Step 2 : Create client side custom formatter dmx.Formatter('array','stringify', function(val) { return JSON.stringify(val); }); Step 3: Create simple form on your page <form id="formTest" method="post" is="dmx-serverconnect-form" action="./dmxConnect/api/BackOfficeV2/Testler/apiCatcher.php"> <div class="form-row"> …

[mgaussie]

Thanks @bpj - I’m going to implement the stringify custom formatter for something else - but this flow is all Server Side - and the formatter provided in this post is client side only as I understand it

[bpj]

Ah ok. The process for doing formatters was in a guide I did but was for PHP.

Creating Custom Formatters (PHP and client-side)

I know you use Node so maybe one of the Wappler team (@teodor, @patrick, @George) could add the location, process etc. for Node? I’m sure most of creating a formatter is the same, just where to place it.

[mgaussie]

Very strangely, I included the custom formatter in ‘Formatters’ - copied exactly from that post - and it broke the entire project! Wouldn’t load on local server… the moment I removed it, everything was back to normal!

[mgaussie]

Got it working, thanks to @sid for the inspiration - while I ended up with a different workflow, this provided the inspiration I needed.

For anyone reading that wants to do something similar:
I used a single repeat, and stripped back all JSON to just the ‘params’ part of the JSON only. Then I used the output of the repeat in the API call, but appended the ‘requests’ part of the JSON prior to the dynamic output, it works perfectly!

[sid]

Using the repeat and concatenating values is basically what the idea I was trying to explain.
Glad to see you could make it work with the API.