Escape character appears in JSON output-CodePudding

I'm writing to an Avro file (which is being sent to Snowflake) and one of the fields is a blob of JSON.

This JSON is a collection of elements and values. The shape of this JSON will vay and isn't known until run time so I cannot make a object of the elements/values and use serialize/deserialize to turn it into a JSON string.

The approach I've taken is to use StringBuilder to append the elements and values.

StringBuilder newJson = new StringBuilder();
newJson.Append("{");
foreach (Field requestedField in fields)
{
   newJson.Append($"\"{requestedField.Name}\": {FormatValue(requestedField.Value)}");
   if (requestedField != fields.Last())
     newJson.Append(", ");
}
newJson.Append("}");

The problem is that when this reached Snowflake is has the escape character backslash in it, and I don't know how. I've tried the obvious

newJson.ToString().Replace(@"\", string.Empty);

I never thought that would work, and I was right.

EDIT: Added Field class

public class Field { 
  public string Name { get; set; } 
  public string Value { get; set; } 
}

I kept the Value type as String as I don't know what is would be until presented with the data from the SQL table.

So, to explain further,

List<Field> fields = new List<Field>();

Would be a single row in a SQL table, each Field in that List would be a field in that row and the data type of the field isn't know until runtime. Might be bit, varchar, int etc.

If at runtime the user present a table like:

ProductName varchar(50)
Price int
OrderCode varchar(10)
Deleted bit

Need a JSON output of each row of data in that table like

{
"ProductName": "Bucket",
"Price": 199,
"OrderCode": "G1234",
"Deleted": true
},
{
"ProductName": "Spade",
"Price": 299,
"OrderCode": "G5678",
"Deleted": false
}

The trick is I don't know the schema of the table until the user gives it to me. So with me (clearly wrong) StringBuilder approach I'm getting

{
\"ProductName\": \"Bucket\",
\"Price\": 199,
\"OrderCode\": \"G1234\",
\"Deleted\": true
},
{
\"ProductName\": \"Spade\",
\"Price\": 299,
\"OrderCode\": \"G5678\",
\"Deleted\": false
}

If I serialise the Fields object I get:

{
"Name": "ProductName",
"Value": "Bucket"
}

etc

Snowflake result

CodePudding user response：

I assume that you build a proper JSON object, so I think the problem is how you use the output in Snowflake. Do you use PARSE_JSON() function to parse the JSON object?

select '{ "Test": "Test" }'::VARIANT as res;

 -------------------------- 
|           RES            |
 -------------------------- 
| "{ \"Test\": \"Test\" }" |
 -------------------------- 

select PARSE_JSON('{ "Test": "Test" }')::VARIANT as res;

 --------------------- 
|         RES         |
 --------------------- 
| { "Test": "Test"  } |
 ---------------------

CodePudding user response：

There are multiple ways to do this, but I will give you a solution that more closely matches the approach you already started. If you change your Field class so that Value is of type Object instead of String you can just construct a JSON object and then serialize it to a string.

public class Field { 
  public string Name { get; set; } 
  public Object Value { get; set; } 
}

Assuming your fields variable is a List<Field> similar to this:

 List<Field> fields =  new List<Field>();
 fields.Add(new Field("ProductName", "Bucket"));
 fields.Add(new Field("Price", 199));
 fields.Add(new Field("OrderCode", "G1234"));
 fields.Add(new Field("Deleted", true));

You can do something like this with Json.NET (and also other JSON libraries):

using Newtonsoft.Json;

 JObject json = new JObject();
 foreach (Field requestedField in fields)
 {
      json[requestedField.Name] = JToken.FromObject(requestedField.Value);
 }
 string output = JsonConvert.SerializeObject(json);

The value of output would be a single JSON object:

{
  "ProductName":"Bucket",
  "Price":199,
  "OrderCode":"G1234",
  "Deleted":true
}

If you need to output a JSON array of these objects as if you had a List<List<Field>>:

JArray jsonArray = new JArray();
foreach(List<Field> fields in listOfFieldLists)
{
     JObject json = new JObject();
     foreach (Field requestedField in fields)
     {
        json[requestedField.Name] = JToken.FromObject(requestedField.Value);
     }
     jsonArray.Add(json);
}

string output = JsonConvert.SerializeObject(jsonArray);

The output would be:

[
   {
        "ProductName": "Bucket",
        "Price": 199,
        "OrderCode": "G1234",
        "Deleted": true
    }, 
    {
        "ProductName": "Spade",
        "Price": 299,
        "OrderCode": "G5678",
        "Deleted": false
    }
]

To Keep Your StringBuilder Approach:

Thinking about this further, I would need to know how you are initializing the List<Field> which you don't quite explain. If you don't have access to the database schema, and you can't cast the values to their proper types when you load the data because you don't know their types, then defining Value as type Object will not work. You would have to determine the type based on the string value. That would explain the FormatValue() function you have. It is likely determining whether or not to add double quotes based on if its a number or boolean or not.

In that case, you can still use your StringBuilder output. What you have is what we call "stringified" JSON where all the quotes are escaped so it can be used as variable of a string. In JavaScript you would call JSON.stringify() on an object to get the same result you have. To go back to a real JSON object (which you are asking for) you would call JSON.parse() on your stringified output. To do that in C# you would just parse the stringified JSON from your StringBuilder output using a JSON library like this:

JObject realJson = JObject.Parse(newJson.ToString());

Then you can convert that JSON object to a string in many different ways without "stringifying" it with escaped quotes like this:

string output = JsonConvert.SerializeObject(realJson));

Or:

string output = realJson.ToString(Newtonsoft.Json.Formatting.Indented);