Skip to main content
Solved

OData reader with "$filter" parameter?

S

I'm new here and need to filter data from a Odata feed. The feed contains open data so no need for credentials. When I add an Odata reader and paste below URL, I can choose several feeds. The main one is named: TypedDataSet

​

https://opendata.cbs.nl/ODataFeed/odata/82062NED

​

​The direct link to main Odata feed is: https://opendata.cbs.nl/ODataFeed/odata/82062NED/TypedDataSet

​

It contains data from 2012 tot 2023 April. I only need data from 2022 and higher. I found some tips for filtering like placing

?$filter=Perioden gt 2021MM12 but this doesn't work. I think I'm almost there and only need to figure out the right syntax. Can anyone help me?

Thanks in advanced!

Cheers Richard ​

Best answer by nielsgerrits

nielsgerrits wrote:

Thanks for your complete and reproduceable question. I tried using the OData Reader and FeatureReader.

The FeatureReader has the Where Clause greyed out, so I suspect a filter is not implemented. Could not find a filter option in the Reader either.

 

The raw response is XML, so a workaround is to use a HTTPCaller and use that to do the request, then parse the response body using the XMLFragmenter.

 

The filter syntax is explained here, but I have not got it working with your dataset so far.

Attached sample workspace using a HTTPCaller.

 

View original
Did this help you find an answer to your question?

9 replies

nielsgerrits
VIP
Forum|alt.badge.img+54

Thanks for your complete and reproduceable question. I tried using the OData Reader and FeatureReader.

The FeatureReader has the Where Clause greyed out, so I suspect a filter is not implemented. Could not find a filter option in the Reader either.

 

The raw response is XML, so a workaround is to use a HTTPCaller and use that to do the request, then parse the response body using the XMLFragmenter.

 

The filter syntax is explained here, but I have not got it working with your dataset so far.

redgeographics
Celebrity
Forum|alt.badge.img+49

I've struggled a lot with the CBS API's and their lack of good documentation. Here's a url that works for me:

https://opendata.cbs.nl/ODataApi/odata/70072ned/UntypedDataSet?$filter=RegioS eq 'GM1709'

So it appears to be the ' around the value that does the trick.

nielsgerrits
VIP
Forum|alt.badge.img+54
  • nielsgerritsVIP
  • Best Answer
  • July 11, 2023
nielsgerrits wrote:

Thanks for your complete and reproduceable question. I tried using the OData Reader and FeatureReader.

The FeatureReader has the Where Clause greyed out, so I suspect a filter is not implemented. Could not find a filter option in the Reader either.

 

The raw response is XML, so a workaround is to use a HTTPCaller and use that to do the request, then parse the response body using the XMLFragmenter.

 

The filter syntax is explained here, but I have not got it working with your dataset so far.

Attached sample workspace using a HTTPCaller.

 

S
redgeographics wrote:

I've struggled a lot with the CBS API's and their lack of good documentation. Here's a url that works for me:

https://opendata.cbs.nl/ODataApi/odata/70072ned/UntypedDataSet?$filter=RegioS eq 'GM1709'

So it appears to be the ' around the value that does the trick.

Hello,

 

Thank you but when I paste your URL I can't select a Feature Type to read.

image.pngI've tried the default reader and also the feature reader.

 

What could me wrong?

redgeographics
Celebrity
Forum|alt.badge.img+49
sanchezenschede wrote:

Hello,

 

Thank you but when I paste your URL I can't select a Feature Type to read.

image.pngI've tried the default reader and also the feature reader.

 

What could me wrong?

Don't use the OData reader, use a HTTPCaller instead and work with the _response_body attribtue. That's formatted as JSON so you'll need a JSONFragmenter / JSONFlattener to pull it apart.

S
nielsgerrits wrote:

Thanks for your complete and reproduceable question. I tried using the OData Reader and FeatureReader.

The FeatureReader has the Where Clause greyed out, so I suspect a filter is not implemented. Could not find a filter option in the Reader either.

 

The raw response is XML, so a workaround is to use a HTTPCaller and use that to do the request, then parse the response body using the XMLFragmenter.

 

The filter syntax is explained here, but I have not got it working with your dataset so far.

Thank you Niels, I will give it a try. (Never used the httpcaller before.) I will let you know if I succeed ;-)

S
sanchezenschede wrote:

Hello,

 

Thank you but when I paste your URL I can't select a Feature Type to read.

image.pngI've tried the default reader and also the feature reader.

 

What could me wrong?

Thank you. I will give it a try. Weird though that the default Reader can't handle filters.

S
nielsgerrits wrote:

Attached sample workspace using a HTTPCaller.

 

Wow Niels, your 2 example flows are great and both work!

The first one (feature reader) seems the easy one and has my preference.

The second one with the HTTPCaller also works but seems more complex. This one probably is faster but I don't need a lot of data.

 

So the trick is to start the flow with a Initiate Creator. I never used this before.

image 

Thx again!

Cheers Richard

nielsgerrits
VIP
Forum|alt.badge.img+54
nielsgerrits wrote:

Attached sample workspace using a HTTPCaller.

 

Cheers :)

Reply


Helpful Members This Week

Community Stats

31,247
Posts
118,490
Replies
38,838
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.