Forum Discussion

djsiegel's avatar
djsiegel
Former Employee
4 years ago

Creating elements out of normalized column data for Gravity Forms

I’ve got a JSON ‘entry’ (sample below) that’s coming to me from a rest API, and the elements in that entry consist of all the metadata about a form that the users fill out, as well as all their responses. Each of the user’s responses are normalized by Gravity Forms however, by making a numbered element with the response as the value for each element, and the “_labels” element contains the column or field names for each response.

To get to my target database however, I want to use those values inside the labels element as keys for the values that are at the top level.

I’ve reviewed a couple of posts here from Patrick that do something similar using a double mapping pipeline, and I was able to make my own pipeline using a JSON splitter to get a JSON of just the labels, but I can’t seem to figure out how to pivot or transpose the data to get a tuple to reinsert into the main document. My gut feel is that I need to get the data I want into its own set of documents, then join that to the original.

Essentially I’d like to turn the entries for 1.3:

into an element like this:
"First": "Post",

Sample Entry:

    "entries": [
        {
            "2": "Creating using REST API v2",
            "3": "Manually created using the POST route of the REST API v2",
            "id": "311",
            "form_id": "176",
            "post_id": null,
            "date_created": "2018-10-16 12:43:23",
            "date_updated": "2018-10-16 19:33:56",
            "is_starred": "0",
            "is_read": "1",
            "ip": "::1",
            "source_url": "http://localhost/wp.dev/?gf_page=preview&id=176",
            "user_agent": "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:38.0) Gecko/20100101 Firefox/38.0",
            "currency": "USD",
            "payment_status": null,
            "payment_date": null,
            "payment_amount": null,
            "payment_method": "",
            "transaction_id": null,
            "is_fulfilled": null,
            "created_by": "1",
            "transaction_type": null,
            "status": "active",
            "1.3": "Post",
            "1.6": "Entries 2",
            "1.2": "",
            "1.4": "",
            "1.8": "",
            "_labels": {
                "1": {
                    "1.2": "Prefix",
                    "1.3": "First",
                    "1.4": "Middle",
                    "1.6": "Last",
                    "1.8": "Suffix"
                },
                "2": "Untitled",
                "3": "Untitled"
            }
        },

6 Replies

  • David -

    First use a JSON Splitter to make each array element from “entries” part of the root object:

    Then use this expression in a Mapper snap with target path set as “$”

  • Technically, this could have been accomplished with a single Mapper expression, but I thought the JSON Splitter would make it a little easier to understand the Object.mapKeys() expression.

  • Hi David,

    I have tried to create a sample pipeline that supports multiple labels (ex: 1.1,1.2,1.3,2.1, 2.2 etc). Check the attached pipeline which has the sample data for multiple values. Hope this will help you.
    

    Normalized_Column_Sample_2022_07_08.slp (17.6 KB)

    • bchaubey's avatar
      bchaubey
      New Contributor

      Hi Team, I want to normalize the data like below source
      col1;col2
      ABC;CC1,CC2,CC3
      DEF;CC1,CC2,CC3

      Target should be like below
      col1;col2
      ABC;CC1
      ABC;CC2
      ABC;CC3
      DEF;CC1
      DEF;CC2
      DEF;CC3

      May i know how it is possible in Snaplogic?

      • nsingam's avatar
        nsingam
        Employee

        Hi,

        You can use the below expression a mapper followed by a splitter snap.

        $value.split(“;”)[1].split(“,”).map(x =>$value.split(“;”)[0]+“;”+x)

        in the above expression, $value is assumed to be the actual source value.
        The example source json is as follows.

        [{“value”:“col1;col2”},
        {“value”:“ABC;CC1,CC2,CC3”},
        {“value”:“DEF;CC1,CC2,CC3”}]

  • djsiegel's avatar
    djsiegel
    Former Employee

    @nsingam : Thank you for your example! That’s a good step-by-step example of how someone can change the format of the data for use elsewhere, and even accommodates the other metadata items 2 and 3.

    I do happen to really like @koryknick 's example, however, since it leaves the rest of the object intact, and is a very elegant example of the Object.mapKeys() function! His manages to get to the target system format in 2 snaps. It’s pretty neat that we have both a mapKeys and mapValues in our toolbox to use in situations like this.

    Here’s the sample pipeline that does the transformation.
    GravityFormsReassemble_2022_07_08.slp (8.8 KB)

    Thank you both!