locked
converting array data into a string and then inserting it into a sql table RRS feed

  • Question

  • howdy,

    so im writing a small app to pull data from a webhook and dump it into a sql table. 

    it's formatted, the data's coming through and being put where it needs to go, however.

    some of the data is in arrays. i dont need the array, i just need a string from the array to dump into the table.

    currently my insert strings look something like this:

                            "Question1": "@triggerBody()?['data']?['1'][1]",
                            "Question11": "@triggerBody()?['data']?['11'][1]",
                            "Question12": "@triggerBody()?['data']?['12'][1]",
                            "Question13": "@triggerBody()?['data']?['13'][1]",
                            "Question14": "@triggerBody()?['data']?['14'][1]",
                            "Question15": "@triggerBody()?['data']?['15'][1]",
                            "Question16": "@triggerBody()?['data']?['16'][1]",
                            "Question17": "@triggerBody()?['data']?['17'][1]",
                            "Question2": "@triggerBody()?['data']?['2'][1]",
                            "Question5": "@triggerBody()?['data']?['5'][1]",
                            "Question6": "@triggerBody()?['data']?['6'][1]",
                            "Question7": "@triggerBody()?['data']?['7'][1]",
                            "Question9": "@triggerBody()?['data']?['9'][1]",
                            "agerange": "@triggerBody()?['survey']?['agerange']",
                            "city": "@triggerBody()?['survey']?['city']",
                            "contact_id": "@triggerBody()?['survey']?['contact_id']",
                            "email": "@triggerBody()?['survey']?['email']",
                            "first_name": "@triggerBody()?['survey']?['first_name']",
                            "gender": "@triggerBody()?['survey']?['gender']",
                            "last_name": "@triggerBody()?['survey']?['last_name']",
                            "latitude": "@triggerBody()?['survey']?['latitude']",
                            "longitude": "@triggerBody()?['survey']?['longitude']",
                            "points": "@{triggerBody()?['survey']?['points']}",
                            "state": "@triggerBody()?['survey']?['state']",
                            "submit_count": "@{triggerBody()?['survey']?['submit_count']}",
                            "survey_id": "@triggerBody()?['survey']?['survey_id']",
                            "survey_name": "@triggerBody()?['survey']?['survey_name']",
                            "timestamp": "@{triggerBody()?['survey']?['timestamp']}",
                            "total_points": "@{triggerBody()?['survey']?['total_points']}",
                            "type": "@triggerBody()?['survey']?['type']",
                            "user_id": "@triggerBody()?['survey']?['user_id']",
                            "user_name": "@triggerBody()?['survey']?['user_name']"
                        },

    top ones are arrays, bottom ones are strings. for the most part it works, however some of the arrays have multiple objects in the rows. raw data on the table from the error report: 

    {
      "Question1""Contact",
      "Question11": [
        "Larry Obhof",
        "Matt Huffman"
      ],
      "Question12""Cash",
      "Question13""5.00",
      "Question14""Yes",
      "Question15""Low",
      "Question16": [
        "Safety",
        "Waste"
      ],
      "Question17""Testerino",
      "Question2": [
        "Jobs and Economy",
        "Education Funding"
      ],
      "Question5""Yes",
      "Question6""Test tester",
      "Question7""5555555555",
      "Question9": [
        "Attending Events",
        "Canvassing"
      ],
      "agerange"null,
      "city"null,
      "contact_id""1505310327906",
      "email"null,
      "first_name""Shane",
      "gender"null,
      "last_name""Cram",
      "latitude""",
      "longitude""",
      "points""",
      "state"null,
      "submit_count""1",
      "survey_id""sv_2132c12e3c24465fa4f6a82da693e89e",
      "survey_name""Ohio Canvassing Common Response Graphics",
      "timestamp""1504721239124",
      "total_points""",
      "type""user",
      "user_id""u_c077f57e854646249fe3ef8f35eebcae:1505310327906",
      "user_name""Shane Cram"
    }

    so the data is where it needs to be, pretty sure it's just mad cause it's technically still in an array. how can i get that data into a string and then into that row? do i need to make a function to do that before the insert? how would i do that?

    thanks for your time.

    Wednesday, September 13, 2017 2:28 PM

Answers

  • Hi,

    Question11 is arraylist of string instead of JSON array . so if you want to parse the payload as JSON then convert the arraylist into object through functions and process them to logic Apps .

    Now coming back to your first question , If you are having a JSON array then use foreach on the repetitive header and get item()?['your key'] to get individual values . 

    I would not suggest to take positional value from JSON payload like[0] or [1] from triggerbody instead would suggest to get item() values .


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply

    • Marked as answer by ShaneGenA Thursday, September 14, 2017 12:38 PM
    Wednesday, September 13, 2017 6:33 PM

All replies


  • Assuming data[‘1’][1] is an array you can convert it to string by either nesting inside a “string” function, or adding curly braces (syntactically identical):

    "Question16": "@string(triggerBody()?['data']?['16'][1])", "Question11": "@{triggerBody()?['data']?['11'][1]}", "Question12": "@triggerBody()?['data']?['12'][1]",

    Let me know if that works for you! 

    Wednesday, September 13, 2017 4:23 PM
  • almost works. 

    this line:

    "Question11": "@{triggerBody()?['data']?['11']?[1]}",

    results in the following on server side:

    "Question11""[\"Dave Burke\",\"Emilia Sykes\"]",

    so it's putting it in as text now, there's just a bunch of brackets with it.

    raw payload looks like:

    "11":["Did they fill out a postcard?",["Emilia Sykes","Vernon Sykes"]],

    corresponding array is defined as:

    "11": {"items": {"type": "string"},"type": "array"},

    im assuming my array definition is throwing it off?



    Wednesday, September 13, 2017 4:36 PM
  • Hi,

    Question11 is arraylist of string instead of JSON array . so if you want to parse the payload as JSON then convert the arraylist into object through functions and process them to logic Apps .

    Now coming back to your first question , If you are having a JSON array then use foreach on the repetitive header and get item()?['your key'] to get individual values . 

    I would not suggest to take positional value from JSON payload like[0] or [1] from triggerbody instead would suggest to get item() values .


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply

    • Marked as answer by ShaneGenA Thursday, September 14, 2017 12:38 PM
    Wednesday, September 13, 2017 6:33 PM