Do you know a solid way to Create JSON?

How To
1

Hey ,

Do you know any example to create json out of json?

Because I want my mapped JSON to be filled with escaped characters,

And I already mapped like 96 columns in JSON and I dont want to was my time with mapping in Array Aggregator again.

It keeps giving me this error

image

I’m generating a structure with this and when I try to use it, it gives this error

{
    "ColumnstoChange": [
        {
            "columnId": "status2",
            "columnValue": "{{49.`8`}}"
        },
        {
            "columnId": "datum",
            "title": "Datum Sonderanalyse beantragt",
            "columnValue": {
                "date": "{{49.`80`}}",
                "includeTime": false
            }
        },
        {
            "columnId": "checkbox",
            "columnValue": {
                "checked": "{{if(74.`Sonderanalyse erfolgt?` = 0; null; true)}}"
            }
        }
    ]
}

This is the flow I’m trying to find a solution for;

image
image1946×1057 193 KB

image
image2038×909 132 KB

I’m trying things;

I cannot just fit it to the data structure. When I generate a data structure out of the one given above, it doesnt accept.

This is the generated structure;

image
image798×1270 98.7 KB

image
image785×1317 106 KB

This is Create Json with Json;

image
image1722×669 97 KB

image
image1710×730 102 KB

2

Hey guys,

any thoughts?

3

I guess we’re already addressing this topic in your other, similar, thread? Or is it a similar subject? → Is there a general module to escape characters of a JSON for HTTP Request? - #12 by Loopz

1 Like
4

I might be oversimplifying, but it looks to me from the screenshot following “This is the flow I’m trying to find a solution for” that the issue is that your mapped data contains double quotes.

You have:

“columnValue”: “Otto Fuchs Dülken KM “Staub” AVV-100604”

The double quotes around Staub need to be escaped. It also looks like there’s trailing whitespace or newlines.

In your Parse JSON module, for the relevant mapped parameters, you should wrap those in a function to escape the double quote, and a function to remove trailing whitespace.

The regular replace() function with simple strings doesn’t process the backslash (\) character. But when you use a regex it does.

So that would look something like:

trim(replace(124.D ; /“/g ; \”))

Here, the /"/g is a regex pattern that matches a double quote. The trailing ‘g’ forces it to be applied “globally”, ie to repeat the match as many times as needed.

2 Likes
5

@Loopz ,

Yes, I’ll share a link to connect them to each other

6

@DavidGurr_Make ,

Thanks David!

I’ll try to make it with regex!

7

In the end the regex approach provided by @DavidGurr_Make is probably simpler than my approach suggested in the other thread: Is there a general module to escape characters of a JSON for HTTP Request? - #12 by Loopz

2 Likes
8

What would be more preferable though is to have your full data structure defined, including the ColumnstoChange collection.

The Create JSON module will then automatically handle the escaping of any problem characters in the input strings.

By mapping parameters yourself into JSON text, you run the risk of the JSON equivalent of an SQL injection attack.

9

@DavidGurr_Make ,

You are right, CREATE JSON does this automatically but I cannot fit a data structure into that module. It doesnt accept. My data types are going like this:

10

Things do get complex when you’re dealing with variable JSON formats and you can end up with a lot of modules and/or operations.

In your case, just treating the JSON as a stream of text and manipulating it directly might be the most efficient way.

You could mitigate the JSON code injection risk by also escaping the \ character in the parameter mapping, in the same way as we did the double quote.

I haven’t tested it, but this might work:
trim(replace(replace(**124.D**;/\\/g;\\) ; /“/g ; \”))

3 Likes
11

Hi @onurbolaca,

There is an alternative way to do it via datastructure, but I need to check whether how Create a JSON module handles empty collection.

What you can do is,

Create a DataStructure like this,


{
    "ColumnstoChange": [
        {
            "columnId": "status2",
            "columnValue": "{{49.`8`}}",
            "columnValueTypeDateTime": {
                "date": "{{49.`80`}}",
                "includeTime": false
            },"columnValueTypeCheckbox": {
                "checked": "{{if(74.`Sonderanalyse erfolgt?` = 0; null; true)}}"
            }
        }]

and, so forth for others as well if there are other variants to columnValue. After JSON is created, you then need to use replace to replace,

columnValueTypeDateTime, columnValueTypeCheckbox to columnValue.

I need to test this out as per my previous experience with Make Data Structure, it will return null values if the fields are not set inside the collection. You should be able to use ignore for such cases, but need to review how Make reformat that as well.

2 Likes
12

Hey @DavidGurr_Make ,

My final code piece for all escaped characters to be replaced:

{{replace(replace(replace(replace(replace(replace(replace(49.`3`; "/\n/g"; space); "/\r/g"; space); "/\t/g"; space); "/\f/g"; space); "/\//g"; "/"); "/\\/g"; "\\"); "/""/g"; "\""")}}

I think this should be an inbuild module. No one should deal with this kind of thing in a no-code platform.

My problem was this: Google Sheets outputs the string data without escaping the characters. So I needed to escape them to send them out.

4 Likes
13

@onurbolaca That’s not a bad idea.

If there’s any feature you’d like to request you can add it or vote on other requests here

EDIT: It seems it’s already been suggested - please upvote here!

2 Likes