Creating elements out of normalized column data for Gravity Forms

[djsiegel]

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"
            }
        },

[koryknick]

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 “$”

[nsingam]

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]

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]

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”}]