topic JSON - Select Parent Field in Workato Pros Discussion Board

JSON - Select Parent Field

GarethMHC — Thu, 30 May 2024 04:57:06 GMT

Hi All,

I've tried to find an answer to this one but haven't had success. I have the below JSON structure (with roughly 5,000 records) returned from an API.

{
  "employees": [
    {
      "/work/employeeIdInCompany": {
        "value": 654321
      },
      "work": {
        "employeeIdInCompany": 654321
      },
      "id": "32184129999999999999"
    },
    {
      "/work/employeeIdInCompany": {
        "value": 123456
      },
      "work": {
        "employeeIdInCompany": 123456
      },
      "id": "3218412688888888888"
    },
    ...
  ]
}

What I need to do is pull the longer ID field where the employeeIdInCompany value matches the current search criteria. For example, where the employeeIdInCompany is 123456, I need to pull 3218412688888888888 from the above JSON.

I know how to use where and pluck where the search item is at or above the level of the field to be pulled, but at a bit of a loss where the search item is nested like this.

Thank You

Gareth

Re: JSON - Select Parent Field

gary1 — Thu, 30 May 2024 06:20:10 GMT

It's a bit of an eyesore, but this is about as elegant as Ruby can do it. Ugly or not, it works and it's a single action.

It assumes a few things:

Each object in your "employees" array will always have a "work" object with an "employeeIdInCompany" value.
Each object will always have an "id" value.
Each "employeeIdInCompany" value is unique and will not repeat in the array.

If those three assumptions are okay, then this will probably work for you.

I put your sample payload in a logger message and accessed it from there. Assume "message" in the below is a data pill containing your sample data. If you already have a data pill for "employees", you can just use that instead of "message["employees"]"

message["employees"][message["employees"].pluck("work").pluck("employeeIdInCompany").find_index(123456)]["id"]

It should look like this:

Hope this helps!

Re: JSON - Select Parent Field

GarethMHC — Fri, 31 May 2024 04:52:19 GMT

Awesome, that worked a treat, thank you!