Parse JSON into Data extension

Question

Essentially, I am wanting to pass the below request body and in cloud page, add to a data extension but unsure how I might be able to achieve this.

{
"id": "id1",
"formid" : "formid",
"data":[ {"column_name":"Full Name","string_value":"John Doe"},
                     {"column_name":"User Phone", "string_value":"12345678"},
                     {"column_name":"User Email", "string_value":"abc@123.com"}],
}

I've tested using the below and response was 200OK, just not sure what the next steps might be and seeking guidance if anyone has done something similar.

<script runat="server" language="javascript">
Platform.Load("Core","1");
var jsonpost = Platform.Request.GetPostData()
var json = Platform.Function.ParseJSON(jsonpost);
Write("<br>message: " +json.msg);  
Write("<br>jsonpost: "+jsonpost);
Write("<br>json: "+json);
</script>

Updated
After running the request, the response body is as below:

<br>message: undefined
<br>jsonpost: {
"id": "id1",
"formid" : "formid",
"data":[ {"column_name":"Full Name","string_value":"John Doe"},
                     {"column_name":"User Phone", "string_value":"12345678"},
                     {"column_name":"User Email", "string_value":"abc@123.com"}],
<br>json:

Answer 1

here is some example code to play around with on a Cloudpage. I have taken your input from GetPostData and put it in as a string for ease of testing.

Mind you that this example only works if you the json contains ONE dataset in the payload, because of the structure of the json.

it gets more complicated with several records at once, as you will have to iterate through it. Not sure if that is a requirement, you'd have to specify.

also be advised your input data is not formatted like your data extension from the comment. As per comment you want firstname and lastname separate, but your json delivers them together. This has some potential to give you a headache (double names...).

So I would suggest setting up your DE to match the JSON precisely (>> one field "full name" instead of two separate ones). I am assuming this in my last line of code where I commented out an example Upsert into a DE with fields: email (PK), fullName, Phone.

<script runat="server" language="javascript">
Platform.Load("Core","1");
var jsonpost = '{"id": "id1","formid" : "formid","data":[ {"column_name":"Full Name","string_value":"John Doe"},{"column_name":"User Phone", "string_value":"12345678"},{"column_name":"User Email", "string_value":"abc@123.com"}],}'
var json = Platform.Function.ParseJSON(jsonpost);

Write("<br>jsonpost: "+jsonpost);
Write("<br>parsed and stringified json: "+Stringify(json));
  
var lastname = json.data[0].string_value
var firstname = json.data[1].string_value
var email = json.data[2].string_value
Write("<br>fullName: "+ json.data[0].string_value)
Write("<br>phone: "+ json.data[1].string_value)
Write("<br>email: "+ json.data[2].string_value)
  
//var yourDE = "THIS IS A DATA EXTENSION NAME";
//var rows = Platform.Function.UpsertData(yourDE,["email"],[email],["fullName","phone"],[fullName,phone]);

</script>

the result is displayed as:

jsonpost: {"id": "id1","formid" : "formid","data":[ {"column_name":"Full Name","string_value":"John Doe"},{"column_name":"User Phone", "string_value":"12345678"},{"column_name":"User Email", "string_value":"abc@123.com"}],}
parsed and stringified json: {"id":"id1","formid":"formid","data":[{"column_name":"Full Name","string_value":"John Doe"}, {"column_name":"User Phone","string_value":"12345678"}, {"column_name":"User Email","string_value":"abc@123.com"}]}
fullName: John Doe
phone 1: 12345678
email: abc@123.com 

hope this is a starting point.