Server Action Unpacking Nested API JSON results

[scott]

After reading this discussion How to properly identify JSON API field properties?, I have some questions on how to unpack the nested API JSON results on the server side.

As discussed in Conditional Logic with Validate Data, I have an API where I retrieve data from and then insert it into the mysql database.

The next step in my linking to the API endpoints has several nested JSON parts. For me to be able to insert the data into my mysql database, I somehow need to unpack the data for retrieval and use in my insert statement.

For example, part of the nested JSON has the following:

    "transaction": {
      "transId": "12345",
      "order": {
        "invoiceNumber": "INV00001",
        "description": "some description",
        "purchaseOrderNumber": "PO000001"
      },
      "lineItems": {
        "lineItem": [
          {
            "itemId": "ITEM00001",
            "name": "name of item sold",
            "description": "Description of item sold",
            "quantity": "1",
            "unitPrice": "6.95",
            "taxable": "true"
          },
          {
            "itemId": "ITEM00002",
            "name": "name of item sold",
            "description": "Description of item sold",
            "quantity": "1",
            "unitPrice": "160.99",
            "taxable": "true"
          }
        ]
      }
}

What I would need to do is be able to insert the results into at multiple different tables, for example, one for general transaction info and the other for items purchased. As shown above, one transaction could have multiple items purchased.

Thanks in advance.

[mebeingken]

Hi Scott,

Create a multi-insert database action, and set the expression of the repeat action that is created to be the array of lineItems > lineItem…you’ll be able to use the dynamic picker to drill down to that array.

By way of example of an app I’m working on, which retrieves transactions via API, my repeat expression looks like this:

–Ken

[scott]

I see, thanks @mebeingken.

I had $_Get and NOW code that we preventing me from seeing those items.

Note to self: develop the queries before adding variables.

[scott]

Well, I thought it was working, but it is only half working.

The first repeat (batchList) is working. The data from the API is retrieved and inserted into the database and the last_id value is retrieved.

However, the second repeat (statistics) is not working. This is the nested json result. It appears no action is being taken for this.

[mebeingken]

Wouldn’t you want to be putting the second repeat inside the first repeat? In other words:

Perform these steps for each batchList, and while you are inside a batchList, perform this second set of steps for each of the statistics. The benefit of this, is that you can then tie the statistics back to the batchList they came from using the identity of the first insert.

Also, pull your database connection out of the repeats and just set it once up top – it can be reused throughout.

All that being said, I would think what you have should work. If you post the actual json coming in from the api, and the details of your insert (the one not working,) maybe we can spot the trouble.

[scott]

Ok made the suggested changes.

I have two tables, the batch_list (which data is being inserted into from the first repeat) and the second table, batch_list_statistics (which no data is being inserted into).

The insert for the second table is:

The meta from the json is:

        "meta": [
          {
            "type": "object",
            "name": "data",
            "sub": [
              {
                "type": "array",
                "name": "batchList",
                "sub": [
                  {
                    "type": "text",
                    "name": "batchId"
                  },
                  {
                    "type": "text",
                    "name": "settlementTimeUTC"
                  },
                  {
                    "type": "text",
                    "name": "settlementTimeLocal"
                  },
                  {
                    "type": "text",
                    "name": "settlementState"
                  },
                  {
                    "type": "text",
                    "name": "paymentMethod"
                  },
                  {
                    "type": "text",
                    "name": "marketType"
                  },
                  {
                    "type": "text",
                    "name": "product"
                  },
                  {
                    "type": "array",
                    "name": "statistics",
                    "sub": [
                      {
                        "type": "text",
                        "name": "accountType"
                      },
                      {
                        "type": "number",
                        "name": "chargeAmount"
                      },
                      {
                        "type": "number",
                        "name": "chargeCount"
                      },
                      {
                        "type": "number",
                        "name": "refundAmount"
                      },
                      {
                        "type": "number",
                        "name": "refundCount"
                      },
                      {
                        "type": "number",
                        "name": "voidCount"
                      },
                      {
                        "type": "number",
                        "name": "declineCount"
                      },
                      {
                        "type": "number",
                        "name": "errorCount"
                      }
                    ]
                  }
                ]
              },
              {
                "type": "object",
                "name": "messages",
                "sub": [
                  {
                    "type": "text",
                    "name": "resultCode"
                  },
                  {
                    "type": "array",
                    "name": "message",
                    "sub": [
                      {
                        "type": "text",
                        "name": "code"
                      },
                      {
                        "type": "text",
                        "name": "text"
                      }
                    ]
                  }
                ]
              }
            ]
          }

[mebeingken]

Couple things you can try:

1. You don’t need the last_id set value action as you can just point directly to the identity of the record_insert for your batchId (unless of course you are using that elsewhere.)

2. The second repeat I believe can be {{statistics}}

If those don’t work, think about any indexes on the table that might be preventing it from inserting the record (required fields, related tables, etc.) You can also set values with output on, within that second repeat, so you can see what activity is taking place in order to troubleshoot.

[scott]

@mebeingken Thank you so much, that looks like it resolved the issue.

[mgaussie]

@mebeingken this looks perfect for what I’m trying to do right now with data returned from an API - but I cannot see anywhere in Server Connect how to add objects, arrays and variables underneath the API Action! Does this still operate this way?

Referring to your image:

[mebeingken]

The data and headers are filled in when you use the Define Schema. In there you can either build that schema from a sample call, or you can provide a sample json response. As long as you provide valid json, you can create whatever you’d like (assuming of course it matches the response(s) from the api.

[mgaussie]

UPDATE: Ok, I see where this is now. It appears when I try to get data from the API - and not in the actual Server Connect action steps as per my image. Thank you

Now I need to figure out how to expand the array and run conditions on it:

[mebeingken]

Anything in data & headers should be available in data bindings.

Here is a basic example:

In the api definition

Chosen in a repeat data binding

Now in looking at this, we can see that while the categories array is available (and what I needed) the objects within that array are not – which suggests a bug (which I would try to confirm in a sandbox project.)

[mgaussie]

Yep, that’s what I’m seeing.

This is the schema:

But in my API action:

So this might perhaps be a bug then?

[mebeingken]

I would keep going with the actual need before reporting it ( unless of course you are convinced already ).

You have an array at the root of the response, which suggests you use a repeat to loop through that. The repeat will be able to see the results array, so then you check within the repeat to see if the children of results then show up.

[mgaussie]

Looks like it’s been reported in another post by Teo, assigning George to it. Even the repeat isn’t showing the full array unfortunately, so i’ll have to move onto another part of the project until the team can hopefully put a fix out there.

But, as always, thank you for your help @mebeingken.