JSON Objects within Dynamic Objects

[sovereignautos]

Good morning,

After submitting an HTTP API POST request, I am using a ParseJSON on the response.
I have attached an example of the ParseJSON output

#1 - I have been running into issues trying to process this output as I am told it's an Object rather than an Array.

#2 - The Output data contains the necessary information (eg: "user_id", "client_id", etc), but each group is nested under another level which is named the same as the user_id. Therefore each item is nested under a differently named level.
I have been unable to extract this data into anything useable.

I am looking to get an array output of the displayed fields.

Any help would be greatly appreciated!

{
  "results": {
    "payroll_report": {
      "212691": {
        "user_id": 212691,
        "client_id": "443262",
        "start_date": "2024-01-20",
        "end_date": "2024-01-31",
        "total_re_seconds": 199800,
        "total_pto_seconds": 0,
        "total_work_seconds": 235800,
        "total_paid_break_seconds": 0,
        "total_unpaid_break_seconds": 0,
        "pto_seconds": {},
        "overtime_seconds": {
          "2": 0,
          "1.5": 36000
        },
        "timesheet_count": 16
      },
      "676634": {
        "user_id": 676634,
        "client_id": "443262",
        "start_date": "2024-01-20",
        "end_date": "2024-01-31",
        "total_re_seconds": 139500,
        "total_pto_seconds": 0,
        "total_work_seconds": 139500,
        "total_paid_break_seconds": 0,
        "total_unpaid_break_seconds": 0,
        "pto_seconds": {},
        "overtime_seconds": {
          "2": 0,
          "1.5": 0
        },
        "timesheet_count": 6
      },
      "725666": {
        "user_id": 725666,
        "client_id": "443262",
        "start_date": "2024-01-20",
        "end_date": "2024-01-31",
        "total_re_seconds": 212460,
        "total_pto_seconds": 0,
        "total_work_seconds": 275400,
        "total_paid_break_seconds": 0,
        "total_unpaid_break_seconds": 0,
        "pto_seconds": {},
        "overtime_seconds": {
          "2": 0,
          "1.5": 62940
        },
        "timesheet_count": 14
      }
}
}

[jitheshk]

Hi,

Instead of using ParseJSON, use expressions.
for instance, if this output is a Variable of type object. You can add 'Compose' Action and give the expression below to get the value of User ID under (results/payroll_report/212691):

variables('NewObj')?['results']?['payroll_report']?['212691']?['user_id']

(refer the flow below)

[sovereignautos]

Thanks for this, but i dont know all the IDs that might exists in the report (the 212691 number) so i cant reference that in the expression. 

[SanmeshG]

Hi @sovereignautos ,

You can check if the property exists in the JSON or not with the expression/function empty().

If you are sure that the property name will be same then you can use the below example and see if it works.

example JSON :

{
  "results": {
    "employeeDetails": {
      "name": "John",
      "lastname": "Doe"
    }
  }
}

In the above example I check if the property employeeDetails exists or not and then retrieve the value. You can do the same in your JSON.

if(empty(outputs('Compose')?['results']?['employeeDetails']),null,outputs('Compose')?['results']?['employeeDetails']?['name'])

If this helps , please give this a thumbs up and mark this as a solution.

Thanks,

Sanmesh

[sovereignautos]

Sorry not sure how this would help extract these fields for each one of the objects, can you give me any more indication? 

cheers 

        "client_id": "443262",
        "start_date": "2024-01-20",
        "end_date": "2024-01-31",
        "total_re_seconds": 199800,
        "total_pto_seconds": 0,
        "total_work_seconds": 235800,
        "total_paid_break_seconds": 0,
        "total_unpaid_break_seconds": 0,
        "pto_seconds": {},
        "overtime_seconds": {
          "2": 0,
          "1.5": 36000
}

[jitheshk]

Follow the below steps as-it-is: (in the step 5 you will be able to get your desired output)

Step 1: I have initialized a variable as Object "NewObj" and copied the entire JSON object shared by you.
Step 2: Compose : 

skip(split(string(variables('NewObj')?['results']?['payroll_report']),'"user_id":'),1)

Step3: Initialize array variable "NewArray"

Step4: Apply to Each (pass the output of Compose)

Step4.1: Inside Apply to each> Compose1 :

split(items('Apply_to_each'),',')?[0]

Step 4.2:Inside Apply to each>  Append to array variable "NewArray" : output of Compose1

Step5: Apply to each (pass the output of "NewArray")

Step5.1:Compose: 

variables('NewObj')?['results']?['payroll_report']?[items('Apply_to_each_1')]?['user_id']

Step5.2:Compose: 

variables('NewObj')?['results']?['payroll_report']?[items('Apply_to_each_1')]?['client_id']

Step5.2:Compose: 

variables('NewObj')?['results']?['payroll_report']?[items('Apply_to_each_1')]?['start_date']

[SanmeshG]

Once the expression , with empty function returns true you can try accessing the properties that you are looking for

[Chriddle]

Compose (your JSON)

Spoiler

{
    "results": {
        "payroll_report": {
            "212691": {
                "user_id": 212691,
                "client_id": "443262",
                "start_date": "2024-01-20",
                "end_date": "2024-01-31",
                "total_re_seconds": 199800,
                "total_pto_seconds": 0,
                "total_work_seconds": 235800,
                "total_paid_break_seconds": 0,
                "total_unpaid_break_seconds": 0,
                "pto_seconds": {},
                "overtime_seconds": {
                    "2": 0,
                    "1.5": 36000
                },
                "timesheet_count": 16
            },
            "676634": {
                "user_id": 676634,
                "client_id": "443262",
                "start_date": "2024-01-20",
                "end_date": "2024-01-31",
                "total_re_seconds": 139500,
                "total_pto_seconds": 0,
                "total_work_seconds": 139500,
                "total_paid_break_seconds": 0,
                "total_unpaid_break_seconds": 0,
                "pto_seconds": {},
                "overtime_seconds": {
                    "2": 0,
                    "1.5": 0
                },
                "timesheet_count": 6
            },
            "725666": {
                "user_id": 725666,
                "client_id": "443262",
                "start_date": "2024-01-20",
                "end_date": "2024-01-31",
                "total_re_seconds": 212460,
                "total_pto_seconds": 0,
                "total_work_seconds": 275400,
                "total_paid_break_seconds": 0,
                "total_unpaid_break_seconds": 0,
                "pto_seconds": {},
                "overtime_seconds": {
                    "2": 0,
                    "1.5": 62940
                },
                "timesheet_count": 14
            }
        }
    }
}

Select (gets the property names)

From

xpath(
	xml(json(concat('{"Root":', outputs('Compose'), '}'))),
	'//results/payroll_report/*'
)

Map

replace(
	xpath(item(),'name(/*)'),
	substring(
		xpath(item(),'name(/*)'),
		0,
		7
	),
	string(
		sub(
			int(
				substring(
					xpath(item(),'name(/*)'),
					4,
					2
				)
			),
			30
		)
	)
)

Select 2

From

body('Select')

Map

outputs('Compose')['results/payroll_report'][item()]

[Chriddle]

Just read: "... which is named the same as the user_id" 🤣

That makes it easy

Compose

Same as above

Select

From

xpath(
	xml(json(concat('{"Root":', outputs('Compose'), '}'))),
	'//results/payroll_report/*/user_id/text()'
)

Map

outputs('Compose')['results/payroll_report'][item()]