Filtering with ParseJSON

1

Lets say I have a table of events, and each event has a column with a json object with two pairs, “date” and “attendees”, which holds the history of the events.

How can I use ParseJSON() to filter the json to only return events with more than 5 attendees?

2

Just because nobody else will probably answer this because somehow JSONPath is overlooked in any learning materials…

If your JSON is like this:

{
    "events" : [
        {
            "name" : "Small event",
            "attendees" : [
                {"name": "Paul"},
                {"name": "Cathy"}
            ]
        },
        {
            "name" : "Large event",
            "attendees" : [
                {"name": "Paul"},
                {"name": "Alex"},
                {"name": "Joe"},
                {"name": "Nadia"},
                {"name": "Maria"},
                {"name": "Tracy"}
            ]
        }
    ]
}

then to filter down for events that have more than 5 attendees, use this JSONPath:

$.events[?(@.attendees.length > 5)]

For everyone’s reference:

  • $ is like thisTable (or thisJson actually)
  • [?( ... )] is like .Filter(...)
  • and @ is like CurrentValue :slight_smile:
5 Likes
3

jason path syntax is explained here

2 Likes
4

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.