cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tommy-Upton
Frequent Visitor

Constructing JSON with sub Table

I'm constructing JSON to pass to the Bing Maps API.  I'm using a flow to do the heavy lifting, but I'm struggling with getting the JSON properly formatted.  The issue is with a subtable field inside of the main record.  You can add a "[" to the collection and it will properly format when you use JSON formula in PA.  However, instead of just adding the subtable it adds "Value: {" before the subtable which is improperly formatted JSON.

 

Here is a snippet of code that I'm looking to create (properly formatted).  The item of concern is after "shifts"

 

 

{
    "agents": [
        {
            "name": "agentName",
            "shifts": [
                {
                    "startTime": "2019-11-09T08:00:00",
                    "startLocation": {
                        "latitude": 47.694117204371,
                        "longitude": -122.378188970181
                    },
                    "endTime": "2019-11-09T18:00:00",
                    "endLocation": {
                        "latitude": 47.7070790545669,
                        "longitude": -122.355226696231
                    },
                    "breaks": [{
                            "startTime": "2019-11-09T12:00:00",
                            "endTime": "2019-11-09T14:00:00",
                            "duration": "00:30:00"
                        },
                        {
                            "startTime": "2019-11-09T16:00:00",
                            "endTime":"2019-11-09T16:30:00"    
                        }
                    ]
                }
            ],

 

 

 Here is what happens if I replicate the structure in PowerApps.

 

 

{
  "agents": [
    {
      "name": "DOE,JANE",
      "shifts": [
        {
          "Value": {
            "address": "8000 65TH AVENUE NORTH, BROOKLYN PARK, MN 55428",
            "endTime": "2022-08-09T17:00:00",
            "startTime": "2022-08-09T08:00:00"
          }
        }
      ]
    },

 

 

Here is the snippet of code where I'm collecting the data that creates the above JSON:

 

 

ForAll(Distinct(CollTest,Resource),
    Collect(CollAgentsData,
        {name:Result,
            shifts: [
                {startTime:"2022-08-09T08:00:00",
                address:LookUp(CollAgentAddresses,ResourceName=Result).Address, 
                endTime:"2022-08-09T17:00:00"
                }
            ]
        }
    )
);

 

 

How do I get rid of that extra Value: { section after shifts: ?

1 REPLY 1
MarvinBangert
Super User
Super User

Hey @Tommy-Upton 

could you please try to replace the

shifts: [
    {...},
    {...}
]

with

shifts: Table(
    {...},
    {...}
)

This should create one table not two tables within itself and should eliminate the "value:".

 

Here is an example:

MarvinBangert_0-1660167675550.png

Result:

MarvinBangert_1-1660167845913.png

 

Does this help you? Otherwise please give me some more information.

Best regards
Marvin

If you like this post, give a Thumbs up. If it solved your request, Mark it as a Solution to enable other users to find it.

Blog: Cloudkumpel

 

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (1,716)