Skip to main content
Solved

OData reader with "$filter" parameter?

  • July 10, 2023
  • 9 replies
  • 197 views
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

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.

 

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

9 replies

nielsgerrits
VIP
Forum|alt.badge.img+60
  • nielsgerritsVIP
  • 2938 replies
  • July 10, 2023

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.

Buckle your seatbelt Dorothy, cause Kansas is going bye-bye...
redgeographics
Celebrity
Forum|alt.badge.img+59
  • redgeographicsCelebrity
  • Celebrity
  • 3700 replies
  • July 11, 2023

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.

FME rocks! \m/
nielsgerrits
VIP
Forum|alt.badge.img+60
  • nielsgerritsVIP
  • 2938 replies
  • Best Answer
  • July 11, 2023

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.

 

Buckle your seatbelt Dorothy, cause Kansas is going bye-bye...
S

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+59
  • redgeographicsCelebrity
  • Celebrity
  • 3700 replies
  • July 11, 2023

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.

FME rocks! \m/
S

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

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

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+60
  • nielsgerritsVIP
  • 2938 replies
  • July 11, 2023

Attached sample workspace using a HTTPCaller.

 

Cheers :)

Buckle your seatbelt Dorothy, cause Kansas is going bye-bye...

Community Stats

32,070
Posts
121,771
Replies
39,696
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.