Complex Collection of a Collection

[seadude]

Hello!

Looking for some insights on this one... not sure how its done and tried a few things that didn't work.
Scenario: I have a table with the schema below. I want to Collect a new collection of ONLY the first two items under the "coordinates" subtable.

{
    "type": "FeatureCollection",
    "metadata": {
        "generated": 1555194002000,
        "url": "https://earthquake.usgs.gov/fdsnws/event/1/query?format=geojson&starttime=2014-01-01&endtime=2014-01-02&latitude=47&longitude=-122&maxradiuskm=300&orderby=magnitude-asc",
        "title": "USGS Earthquakes",
        "status": 200,
        "api": "1.8.1",
        "count": 9
    },
    "features": [
        {
            "type": "Feature",
            "properties": {
                "mag": -0.33,
                "place": "27km NNW of Packwood, Washington",
                "time": 1388615267370,
                "updated": 1469215317030,
                "tz": -480,
                "url": "https://earthquake.usgs.gov/earthquakes/eventpage/uw60664496",
                "detail": "https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=uw60664496&format=geojson",
                "felt": null,
                "cdi": null,
                "mmi": null,
                "alert": null,
                "status": "reviewed",
                "tsunami": 0,
                "sig": 2,
                "net": "uw",
                "code": "60664496",
                "ids": ",uw60664496,",
                "sources": ",uw,",
                "types": ",general-link,geoserve,nearby-cities,origin,phase-data,",
                "nst": 9,
                "dmin": 0.01894,
                "rms": 0.08,
                "gap": 71,
                "magType": "md",
                "type": "earthquake",
                "title": "M -0.3 - 27km NNW of Packwood, Washington"
            },
            "geometry": {
                "type": "Point",
                "coordinates": [
                    -121.7506667,
                    46.8501667,
                    1.436
                ]
            },
            "id": "uw60664496"
        },
        {
            "type": "Feature",
            "properties": {
                "mag": 0.19,
                "place": "22km S of Sequim, Washington",
                "time": 1388595112000,
                "updated": 1469215316190,
                "tz": -480,
                "url": "https://earthquake.usgs.gov/earthquakes/eventpage/uw60664386",
                "detail": "https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=uw60664386&format=geojson",
                "felt": null,
                "cdi": null,
                "mmi": null,
                "alert": null,
                "status": "reviewed",
                "tsunami": 0,
                "sig": 1,
                "net": "uw",
                "code": "60664386",
                "ids": ",uw60664386,",
                "sources": ",uw,",
                "types": ",cap,general-link,geoserve,nearby-cities,origin,phase-data,",
                "nst": 3,
                "dmin": 0.1492,
                "rms": 0.22,
                "gap": 306,
                "magType": "md",
                "type": "earthquake",
                "title": "M 0.2 - 22km S of Sequim, Washington"
            },
            "geometry": {
                "type": "Point",
                "coordinates": [
                    -123.108,
                    47.881,
                    4.572
                ]
            },
            "id": "uw60664386"
        },
        {
            "type": "Feature",
            "properties": {
                "mag": 0.22,
                "place": "21km NNW of Amboy, Washington",
                "time": 1388536764680,
                "updated": 1469215315430,
                "tz": -480,
                "url": "https://earthquake.usgs.gov/earthquakes/eventpage/uw60664276",
                "detail": "https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=uw60664276&format=geojson",
                "felt": null,
                "cdi": null,
                "mmi": null,
                "alert": null,
                "status": "reviewed",
                "tsunami": 0,
                "sig": 1,
                "net": "uw",
                "code": "60664276",
                "ids": ",uw60664276,",
                "sources": ",uw,",
                "types": ",cap,general-link,geoserve,nearby-cities,origin,phase-data,",
                "nst": 9,
                "dmin": 0.08565,
                "rms": 0.1,
                "gap": 182,
                "magType": "md",
                "type": "earthquake",
                "title": "M 0.2 - 21km NNW of Amboy, Washington"
            },
            "geometry": {
                "type": "Point",
                "coordinates": [
                    -122.5188333,
                    46.0926667,
                    13.97
                ]
            },
            "id": "uw60664276"
        },
        {
            "type": "Feature",
            "properties": {
                "mag": 0.43,
                "place": "19km E of Kelso, Washington",
                "time": 1388577684900,
                "updated": 1469215315950,
                "tz": -480,
                "url": "https://earthquake.usgs.gov/earthquakes/eventpage/uw60664346",
                "detail": "https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=uw60664346&format=geojson",
                "felt": null,
                "cdi": null,
                "mmi": null,
                "alert": null,
                "status": "reviewed",
                "tsunami": 0,
                "sig": 3,
                "net": "uw",
                "code": "60664346",
                "ids": ",uw60664346,",
                "sources": ",uw,",
                "types": ",cap,general-link,geoserve,nearby-cities,origin,phase-data,",
                "nst": 13,
                "dmin": 0.06314,
                "rms": 0.15,
                "gap": 143,
                "magType": "md",
                "type": "earthquake",
                "title": "M 0.4 - 19km E of Kelso, Washington"
            },
            "geometry": {
                "type": "Point",
                "coordinates": [
                    -122.6526667,
                    46.1491667,
                    15.17
                ]
            },
            "id": "uw60664346"
        }
}

JUST THE FIRST TWO ITEMS in the "Coordinates" subtable. I want a Collection that looks like:

{id: 1, long: -122.6526667, lat: 46.1491667}
{id: 2, long: -122.5188333, lat: 46.0926667}
{id: 3, long: -123.108, lat: 47.881}

Tried so far:

ClearCollect(
    colSecondCollection,
    First(colFirstCollection.features).features.geometry).geometry
); 
// Results in a table of nested tables. I need the coordinates alone
// Also tried:

ForAll(
    colFirstCollection,
    ClearCollect(
        colSecondCollection,
        First(First(colFirstCollection.features).features.geometry).geometry.coordinates).coordinates
    )
);

// Error:  "this action is not allowed within a ForAll"

Anyone have some idears?

Thank yoU!

[RandyHayes]

@seadude 

You can get part of the way there with this formula:

ClearCollect(finalResults, 
   ForAll(Ungroup(colFirstCollection.features, "features"), 
     {id:Value.id, long:First(Value.geometry.coordinates.Value).Value, lat:Last(FirstN(Value.geometry.coordinates.Value,2)).Value}
  )
)

This will return the record table like you wanted - with one exception...the sequential ID numbering.  In the above formula, you will get the id that is in the original JSON payload not a sequence.

Trying to do sequential numbering in PowerApps is oddly challenging.

I hope this puts you on the right track.

[RezaDorrani]

@seadude 

Clear(colSecondCollection);ForAll(colFirstCollection.features,Collect(colSecondCollection,features.Value));Clear(colThirdCollection);ForAll(colSecondCollection,Collect(colThirdCollection,Value.geometry))

This will give you the main collection colThirdCollection which will contain all the coordinates

Now, if you only need the first 2 coordinates then use 

First(colThirdCollection).coordinates.Value - First coordinates

Last(FirstN(colThirdCollection,2)).coordinates.Value - Second coordinates

And to fetch individual values for the coordinates

Example : coordinates: [
-121.7506667,
46.8501667,
1.436
]

First(First(colThirdCollection).coordinates.Value).Value    =  -121.7506667

Last(FirstN(First(colThirdCollection).coordinates.Value,2)).Value   =  46.8501667

Last(FirstN(First(colThirdCollection).coordinates.Value,3)).Value = 1.436

Hope this helps

[seadude]

Hi @RandyHayes and @RezaDorrani ,

Thank you both for the thoughtful replies. This was the easiest for me to implement:

ForAll(
    Ungroup(colEarthquakeResults.features, "features"),
        Collect(colEarthquakeLatLongs,
            {long: First(geometry.coordinates.Value).Value, lat: Last(FirstN(geometry.coordinates.Value,2)).Value}
        )
)

It results in:

@RandyHayes, you were right! It is not easy to implement an incremental ID column in this case! Here's what I tried so far:

ClearCollect(colEarthquakeLatLongs,
    AddColumns(
        ForAll(
            Ungroup(colEarthquakeResults.features, "features"),
            {long:First(geometry.coordinates.Value).Value, lat:Last(FirstN(geometry.coordinates.Value,2)).Value}
        ),
        "id",
        If(
            1 = 1,
            CountRows(colEarthquakeLatLongs) + 1
        )
    )
);

ClearCollect(colEarthquakeLatLongs,
        ForAll(
            Ungroup(colEarthquakeResults.features, "features"),
            {id: Text(CountRows(colEarthquakeLatLongs)) + 1, long: First(geometry.coordinates.Value).Value, lat:Last(FirstN(geometry.coordinates.Value,2)).Value}
        )
    )

ForAll(
    Ungroup(colEarthquakeResults.features, "features"),
        Collect(colEarthquakeLatLongs,
            {id: Value(CountRows(Ungroup(colEarthquakeResults.features, "features"))) + 1, long: First(geometry.coordinates.Value).Value, lat: Last(FirstN(geometry.coordinates.Value,2)).Value}
        )
)

ClearCollect(colEarthquakeLatLongs,
    AddColumns(
        ForAll(
            Ungroup(colEarthquakeResults.features, "features"),
            {long:First(geometry.coordinates.Value).Value, lat:Last(FirstN(geometry.coordinates.Value,2)).Value}
        ),
        "id",
        If(
            !IsEmpty(First(First(First(colEarthquakeResults.features).features.geometry).geometry.coordinates)),
            Text(Value(varCounter) + 1)
        )
    )
)

I bet @mr-dang knows how to do this! 🙂

[RandyHayes]

@seadude 

Well, here's the loaded question...do you have any idea on how many records you anticipate?

If you have knowledge that there is a preset amount or at least an amount of rows that will never exceed a certain number, then you can alter the formula like this:

ClearCollect(colEarthquakeLatLongs,
             ForAll([1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30],
                    If(Value<=CountRows(Ungroup(colEarthquakeResults, "features")),
                        {id:Value, 
                         long:First(Last(FirstN(Ungroup(colEarthquakeResults, "features"), Value)).Value.geometry.coordinates).Value, 
                         lat:Last(FirstN(Last(FirstN(Ungroup(colEarthquakeResults, "features"), Value)).Value.geometry.coordinates,2)).Value
                        } 
                      )
                   )
            )

Of course, in this case, that gives you only a max of 30 sequential ID's...but you could change that if needed.

See if that fits better for you.

[seadude]

Hi @RandyHayes ,

There is no way of knowing exactly how many results will be returned.

• In this blog post, I found a fun way to create a large Numbers Table.
• Without a way to programatically create a Value Table based on CountRows or the like, we'll always run into this issue. Bummer!

Ended up going with this MONSTER!:

Set(varNumTable, [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]);

Clear(colNumbersTable);

Clear(colRecordToCount);

ClearCollect(colNumTemp, varNumTable);

ForAll(colNumTemp,
    ForAll(colNumTemp,
        ForAll(colNumTemp,
            Collect(colRecordToCount,
                {
                    record: 1
                }
            );
            Collect(colNumbersTable,
                {
                    number: CountRows(colRecordToCount)
                }
            )
        )

    )
);

ClearCollect(
    colEarthquakeResults,
    earthquakeAPI.GETearthquakes(
        {
            format: "geojson",
            starttime: Today() - 1,
            endtime: Today(),
            latitude: Location.Latitude,
            longitude: Location.Longitude,
            maxradiuskm: 1000,
            orderby: "magnitude-asc"
        }
    )
);

ClearCollect(colEarthquakeLatLongs,
    ForAll(colNumbersTable,
        If(number <= CountRows(Ungroup(colEarthquakeResults.features, "features")),
            {
                id: number, 
                long: First(
                          Last(
                              FirstN(
                                  Ungroup(colEarthquakeResults.features, "features"), 
                                  number
                              )
                          ).geometry.coordinates
                      ).Value, 
                lat: Last(
                         FirstN(
                             Last(
                                 FirstN(
                                     Ungroup(colEarthquakeResults.features, "features"), 
                                     number
                                 )
                             ).geometry.coordinates,2
                         )
                     ).Value
            } 
        )
    )
)

...all to get an ID column in this Collection!:

@RandyHayesand @RezaDorrani , thanks for the Sunday help! Have a great week!

[RandyHayes]

@seadude 

I just hope we don't have that many earthquakes!!  

Glad things are working now.

[mr-dang]

Thanks for the help here @RezaDorrani @RandyHayes.

One thing I'll add. When I want to understand the structure a little bit more and figure out when I need to use First() Last() etc., I use a JSON viewer:

This one is Code Beautify which is one of the prettier viewers. The collapsing helps to understand things.

[seadude]

@RandyHayes, hep meh! I'm missing the pattern here... Looking to do a similar thing, but this time I have a different schema. Instead of earthquakes, I want to take the results of a Fuzzy Search and plot pins on a map.

Heres the schema:

{
    "summary": {
        "query": "pub",
        "queryType": "NON_NEAR",
        "queryTime": 39,
        "numResults": 10,
        "offset": 0,
        "totalResults": 30,
        "fuzzyLevel": 1,
        "geoBias": {
            "lat": 47.56328695,
            "lon": -122.37870965
        }
    },
    "results": [
        {
            "type": "POI",
            "id": "US/POI/p0/8718662",
            "score": 2.575,
            "dist": 128.26041102796466,
            "info": "search:ta:840539001007346-US",
            "poi": {
                "name": "West Seattle Brewing Company",
                "phone": "+(1)-(206)-7086627",
                "categorySet": [
                    {
                        "id": 9376007
                    }
                ],
                "categories": [
                    "café/pub",
                    "microbrewery/beer garden"
                ],
                "classifications": [
                    {
                        "code": "CAFE_PUB",
                        "names": [
                            {
                                "nameLocale": "en-US",
                                "name": "microbrewery/beer garden"
                            },
                            {
                                "nameLocale": "en-US",
                                "name": "café/pub"
                            }
                        ]
                    }
                ]
            },
            "address": {
                "streetNumber": "4415",
                "streetName": "Fauntleroy Way SW",
                "municipalitySubdivision": "Seattle, Genesee, Fairmount Park",
                "municipality": "Seattle",
                "countrySecondarySubdivision": "King",
                "countryTertiarySubdivision": "Seattle",
                "countrySubdivision": "WA",
                "postalCode": "98126",
                "extendedPostalCode": "981262631",
                "countryCode": "US",
                "country": "United States",
                "countryCodeISO3": "USA",
                "freeformAddress": "4415 Fauntleroy Way SW, Seattle, WA 98126",
                "localName": "Seattle",
                "countrySubdivisionName": "Washington"
            },
            "position": {
                "lat": 47.56428,
                "lon": -122.37784
            },
            "viewport": {
                "topLeftPoint": {
                    "lat": 47.56518,
                    "lon": -122.37917
                },
                "btmRightPoint": {
                    "lat": 47.56338,
                    "lon": -122.37651
                }
            },
            "entryPoints": [
                {
                    "type": "main",
                    "position": {
                        "lat": 47.56412,
                        "lon": -122.37767
                    }
                }
            ]
        }
    ]
}

Problem: I can only get the first set of Lat / Longs from the Fuzzy Search results

Tried so far:

/*====================================================================
Create a numbers table for ForAll looping purposes
====================================================================*/

Set(varNumTable, [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]);

Clear(colNumbersTable);

Clear(colRecordToCount);

ClearCollect(colNumTemp, varNumTable);

ForAll(colNumTemp,
    ForAll(colNumTemp,
        ForAll(colNumTemp,
            Collect(colRecordToCount,
                {
                    record: 1
                }
            );
            Collect(colNumbersTable,
                {
                    number: CountRows(colRecordToCount)
                }
            )
        )

    )
);

/*====================================================================
Collect all nearby "pubs" within a 2mile (~3200 meter) radius
====================================================================*/

ClearCollect(
    colNearbySpots,
    azureMapTimezone.GETSearchFuzzy(1.0,1,3,
        {
            query: "pub",
            lon: Location.Longitude,
            lat: Location.Latitude,
            radius: 3200
        }
    )
);

/*====================================================================
Using the numbers table, create a Collection of all nearby spot Lat/Longs returned from 
the previous API call.

Thank god for the PowerApps forums!: https://powerusers.microsoft.com/t5/General-Discussion/Complex-Collection-of-a-Collection/m-p/267273#M78095
====================================================================*/
ClearCollect(colNearbySpotLatLongs,
    ForAll(colNumbersTable,
        If(number <= CountRows(Ungroup(colNearbySpots.results, "results")),
            {
                id:number, 
                long: First(First(colNearbySpots.results).results.position).position.lon,
                lat: First(First(colNearbySpots.results).results.position).position.lat
            } 
        )
    )
);

/*====================================================================
Using the table of nearby spot Lat/Longs and the numbers table, call the Azure Maps API 
to get a map image of these spots with pins, at each zoom level!
====================================================================*/
Clear(colNearbySpotMaps);

ForAll(
    Filter(colNumbersTable, number >= 10 && number <= 14),
    AddColumns(
        Collect(colNearbySpotMaps,
        azureMapTimezone.GetMapImageWithPins(
            1.0,
            600,
            600,
            {
                zoom: number,
                center: Concatenate(Text(Location.Longitude), ",", Text(Location.Latitude)),
                pins: Concatenate("default||", Concat(colNearbySpotLatLongs, lat & " " & lat, "|")),
                path: Concatenate("ra3200", "||", Text(Location.Longitude), " ", Text(Location.Latitude))
            }
        )
    ) ,
    "id",
    number
    )
);

ClearCollect(colNearbySpotMapsWithIDs,
    ForAll(colNumbersTable,
        If(number <= CountRows(colNearbySpots),
            {
                id:number,
                mapImage: colNearbySpotMaps
            }
        )
    )
)

I also tried First(Ungroup(colNearbySpots.results, "results").position).position.lon (/lat).

I end up with a lat/long list that only shows the first Result copied a bunch:

...and colNearbySpotMapsWithID is nested instead of a flat table:

I know this is a long post 🙂 . Hopefully the patterns that emerge for unfolding JSON will help others too.

Thank you!

[RandyHayes]

@seadude 

So, I breezed through some of the post because I had a question from the start.  I see only one record with a position record in it...is the assumption that you have multiple records in the results array?

I'll start there - I saw some issues with the remaining formulas, but we can cover that after I clarify the above as I don't want to start down a road that is on the road trail.