Escaping Quotes in JSON data

[Greg Hains]

Hi.

Further to the post of:

I have a similar problem with applying escape quotes into text.

Using these script steps:
Set Variable $_outputSelector = "\"" & "Name" & "\"" & "," & "\"" & "MobileNumber" & "\""

JSONSetElement ( ""; ["Filter.Active"  ; "True" ; JSONString] ; ["Filter.OutputSelector"  ; $_outputSelector ; JSONString] )

I get the 'correct' result of:
{ "Filter" : { "Active" : "True", "OutputSelector" : "\"Name\",\"MobileNumber\"" } }

When what I wanted was actually:
{ "Filter" : { "Active" : "True", "OutputSelector" : "Name" , "MobileNumber" } }

The Filemaker variable displays correctly, however extras are added in the JSONSetElement step.)
I also saw other examples where there was an "@" before the variable in the JSONSetElement command but this just gave me errors.

Can somebody please show me where I am going wrong? 

Many thanks in advance,
Greg

 

Edited September 9, 2021 by Greg Hains

[comment]

In the thread you refer to I suggested using the JSONSetElement() function to create all JSON elements, instead of trying to create them using text manipulation - as you do when setting the variable.

I am afraid I cannot tell you how to apply that advice to your case, because the result you show as the wanted result is not valid JSON.

I can say this:

2 hours ago, Greg Hains said:

The Filemaker variable displays correctly, however extras are added in the JSONSetElement step.

Your variable contains the following text:

"Name","MobileNumber"

i.e. a text that contains some literal quotes. When you add such text to a JSON element as JSONString, the quotes must be escaped in order to protect against delimiter collision. This is similar to exporting records where some fields may contain quotes to CSV. Here too the in-field quotes will be escaped in the final result.

 

Edited September 9, 2021 by comment

[Greg Hains]

Hi Comment.

Thanks for your response. 
It was the way I typed it sorry.  This is the verbatim sample from the supplier's website. It is valid JSON.

{
    "Filter": {
        "Active": [
            "True"
        ],
        "OutputSelector": [
            "Name",
            "MobileNumber"
        ]
    }
}

Greg

 

[comment]

That makes more sense. Now, since all the values in this JSON are hard-coded, why do you need to re-create it? Why not simply use it as is?

 

[Greg Hains]

Hi Comment,

I need to have the ability to create the JSON based on variables (or fields) through the JSONSetElement command because there are many other factors earlier in the script that will determine how the JSON will look.  I can't just use static text blocks of JSON.  Does that make sense? That's why I'm trying to understand how the escape quotes are used when creating JSON.

Thanks again,
Greg

[comment]

1 hour ago, Greg Hains said:

there are many other factors earlier in the script that will determine how the JSON will look.  I can't just use static text blocks of JSON.  Does that make sense?

Not really. Could you be more specific about what you have as the starting point and what do you want to end up with as the result?

If you want to produce a JSON with the given structure but with your own values, you could take one of two possible routes:

1. Take the given JSON and replace some or all of its values with your own;
2. Build the JSON from the ground up.

Either way there should be no need for you to escape quotes "manually". You should let Filemaker's built-in functions do the work for you.

 

Edited September 9, 2021 by comment

[Greg Hains]

Hi Comment,

Thanks for sticking with me.

The reason I had to build the JSON by variable was that when I tried to enter more than one value separated by a comma in the JSONSetElement command it threw an error (which is understood because the syntax was broken) so I needed to create that variable (or a field - I don't mind) as a single 'entity' so I could place it in the JSONSetElement command as the "value" parameter.

Short question, how do I create and use the following string (including quotes and comma in between them) with the JSONElement command please to get achieve the JSON block above? 

"Name","MobileNumber"

Cheers,
Greg

 

[comment]

16 minutes ago, Greg Hains said:

Short question, how do I create and use the following string (including quotes and comma in between them) with the JSONElement command please to get achieve the JSON block above? 

Short answer: you do not. The string you show is neither a JSON element, not is it - by your own admission - a string. Otherwise you would want to have it escaped within the JSON, as it was in your initial attempt. What it is is a meaningless and unusable excerpt from the final JSON you are trying to create. And which you should create using some other method.

Now, let me restate the question I asked earlier: do I understand correctly that you want to  produce this JSON block:

{
	"Filter" : 
	{
		"Active" : [ "True" ],
		"OutputSelector" : [ "Name", "MobileNumber" ]
	}
}

but have the values of Name and  MobileNumber replaced by some other values coming from fields in your solution, e.g.

{
	"Filter" : 
	{
		"Active" : [ "True" ],
		"OutputSelector" : [ "John Smith", "555-123-4567" ]
	}
}

 

[bcooney]

I can't imagine why the element "Active" would expect an array of text values?

[Greg Hains]

Hi Comment, BCooney.

The values of Name and MobileNumber have to be just that - I am not populating the JSON with client data, what is happening here is that I am making a call to an major inventory platform to retrieve data from fields that it uses, and have the underlying data returned to me in the response from the Insert From URL command.  There are 40-something fields in this system and I only want the Name and MobileNumber returned. This information is base don their API specifications and I have queried the fact that there was more than one text entry in there. 
I only mentioned two of the fields here (for simplicity) but will need more (of the 40) in the future, so thought I'd start with these just to understand the syntax of the JSONSetElement command.

So, to answer your question above, I do want the verbatim text ("Name" , "MobileNumber") shown in the in the first block, but not the second block where there is a name and number.  I do not know how to get the text below into the JSONSetElement command with those quotes and the comma - hence trying to build a variable in a previous script step using escape quotes.

These are the specs that this platform requires.  It may be possible to generate the correct command in a native CURL command, wasn't having any luck within Filemaker.

Thanks,
Greg

[bcooney]

If you do not need any data from FileMaker fields incorporated into the json that’ll be sent in your request, you could simply paste the json  into the insert text script step and set it to a $var. 

[Greg Hains]

Hi bcooney.

Thanks for coming back to me.

I tried that - see above - but JSONSetElement wasn't very forgiving with the variable I gave it so I figured it wasn't formed in such  way it could use it. It displayed OK when i printed it but JSONSetElement couldn't use it.  So here I am. :)

 

[bcooney]

To be clear, you will not use any json functions to build the json. Just paste valid json into insert text. 

Do you have an example request from the api docs?

[Greg Hains]

Hi bcooney.

Thank you again.

Eureka! There was an error in the syntax of the example the provider had given me. I pushed them for more information when I was getting limited results in my script calls, and got my job escalated, who came back with the amended format, which now works with the original version of the script.

I would like to thank you and @Comment for assisting me.

Cheers,
Greg

 

Edited September 13, 2021 by Greg Hains