To homepage To homepage
Solved

Data capture with multiple API-endpoints

  • 20 November 2023
  • 15 replies
  • 195 views
A
Rank

Hi,

Has anyone worked with API from Energinet?

I need some help extracing data from Energinet. 

Energinet delivers data with multiple endpoints. 

It starts with https://www.energinet.net/api/unit which gives me this 

[
    {
        "unit_id": "1234ABCDfolder",
        "name": "My Company",
        "links": {
            "info": {
                "verb": "GET",
                "href": "/api/unitinfo/1234ABCDfolder"
            },
            "drilldown": {
                "verb": "GET",
                "href": "/api/unit/1234ABCDfolder"
            }
        },
        "datasources": []
    }
]

Now, i have to drill down to get some info: https://www.energinet.net/api/unit/1234ACDfolder

[
    {
        "unit_id": 1Afolder",
        "name": "Company1",
        "links": {
            "info": {
                "verb": "GET",
                "href": "/api/unitinfo/1Afolder"
            },
            "drilldown": {
                "verb": "GET",
                "href": "/api/unit/1Afolder"
            }
        },
        "datasources": []
    },
    {
        "unit_id": "1Bfolder",
        "name": "Company2",
        "links": {
            "info": {
                "verb": "GET",
                "href": "/api/unitinfo/1Bfolder"
            },
            "drilldown": {
                "verb": "GET",
                "href": "/api/unit/1Bfolder"
            }
        },
        "datasources": []
    },
    {
        "unit_id": "1Cfolder",
        "name": "Company3",
        "links": {
            "info": {
                "verb": "GET",
                "href": "/api/unitinfo/1Cfolder"
            },
            "drilldown": {
                "verb": "GET",
                "href": "/api/unit/1Cfolder"
            }
        },
        "datasources": []
    },
        "datasources": []
    }
]

Now, i have to drill down individual Comapnies to get more data: https://www.energinet.net/api/unit/1Afolder

[
    {
        "unit_id": "1AAfolder",
        "name": "West Side",
        "links": {
            "info": {
                "verb": "GET",
                "href": "/api/unitinfo/1Awestfolder"
            },
            "drilldown": {
                "verb": "GET",
                "href": "/api/unit/1Awestfolder"
            }
        },
        "datasources": []
    },
    {
        "unit_id": "1ABfolder",
        "name": "North Side",
        "links": {
            "info": {
                "verb": "GET",
                "href": "/api/unitinfo/1Anothfolder"
            },
            "drilldown": {
                "verb": "GET",
                "href": "/api/unit/1Anorthfolder"
            }
        },
        "datasources": []
    }
]

and so on and so on, until i have run through all the endpoints. There are 10 more levels.

Is there a way to create a datasource from such kind of API an easy way?

 

icon

Best answer by Thomas Lind 21 November 2023, 13:26

View original

15 replies

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @aftabp 

Yes you can use nested calls.

So you start by generating an RSD file on each level you want to connect to.

So if you want to iterate over https://www.energinet.net/api/unit/1Afolder  you will need to pull a list of folders in a call and then use it as explained in the guide.

You can get many levels in one file, so you can have unit folder and unitfolder info in the same rsd file.

Christian Hauggaard
Rank
Userlevel 6
Badge +5

Hi @aftabp 

Were you able to resolve the issue using the guide Thomas mentions above?

A
Rank

@Christian Hauggaard 

Unfortunately, no.

I had a meeting with the vendor and asked if they could provide with custom API to which they agree. 

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @aftabp 

What do they mean with a custom API? Is it some alternative provider and if so what does it return?

A
Rank

Same vendor and same system/application 

We have asked them to create a single data stream instead of multiple endpoints 

I haven’t gotten the endpoint yet so not sure how the data looks like.

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @aftabp 

OK, if you want a suggestion to how it can be done with two URI calls to do what you wanted.

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">
	<!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. -->
	<api:info title="GetAllStopsModified" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1">
		<!-- You can modify the name, type, and column size here. -->
		<attr name="country" 				xs:type="string" 	readonly="false" other:xPath="/json/result/entity/stops/country" 				/>
		<attr name="id" 					xs:type="string" 	readonly="false" other:xPath="/json/result/entity/stops/id" 					/>
		<attr name="lastModificationTime" 	xs:type="datetime" 	readonly="false" other:xPath="/json/result/entity/stops/lastModificationTime" 	/>
		<attr name="lastModifierUserId" 	xs:type="integer" 	readonly="false" other:xPath="/json/result/entity/stops/lastModifierUserId" 	/>
		<attr name="mobileNumber" 			xs:type="string" 	readonly="false" other:xPath="/json/result/entity/stops/mobileNumber" 			/>
		<attr name="orderNumber" 			xs:type="integer" 	readonly="false" other:xPath="/json/result/entity/stops/orderNumber" 			/>
		<attr name="products" 				xs:type="string" 	readonly="false" other:xPath="/json/result/entity/stops/products" 				/>
		<attr name="telephoneNumber" 		xs:type="decimal" 	readonly="false" other:xPath="/json/result/entity/stops/telephoneNumber" 		/>
		<attr name="tenantId" 				xs:type="integer" 	readonly="false" other:xPath="/json/result/entity/stops/tenantId" 				/>
	</api:info>

	<api:set attr="urlbase" value="https://webapi.open.com/api/services/app/TransportFile"/>
	<api:set attr="filein.DataModel" 		value="DOCUMENT" />
	<api:set attr="filein.JSONPath" 		value="$.result.items" /> 
	<api:set attr="filein.URITemplate" 		value="[urlbase]/GetAll"/>
	<api:set attr="filein.ElementMapPath#" 	value="/json/result/items/id" />
	<api:set attr="filein.ElementMapName#" 	value="file_id" />

	<api:set attr="stopin.DataModel" 		value="DOCUMENT" />
	<api:set attr="stopin.EnablePaging" 	value="TRUE" />
	<api:set attr="stopin.JSONPath" 		value="$.result.entity.stops" />
	<api:set attr="stopin.URITemplate" 		value="[urlbase]/GetAllStops?TransportFileId={file_id}"/>

	<!-- The GET method corresponds to SELECT. Here you can override the default processing of the SELECT statement. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
	<api:script method="GET">
		<api:set attr="filein.URI" value="[filein.URITemplate]"/>
		<api:call op="jsonproviderGet" in="filein" out="fileout">
			<api:set attr="stopin.URI" value="[stopin.URITemplate | replace('{file_id}', [fileout.file_id])]"/>
			<api:call op="jsonproviderGet" in="stopin" out="stopout">
				<api:set attr="out.country" 				value="[stopout.country | allownull()]"/>
				<api:set attr="out.id" 						value="[stopout.id | allownull()]"/>
				<api:set attr="out.lastModificationTime" 	value="[stopout.lastModificationTime | allownull()]"/>
				<api:set attr="out.lastModifierUserId" 		value="[stopout.lastModifierUserId | allownull()]"/>
				<api:set attr="out.mobileNumber" 			value="[stopout.mobileNumber | allownull()]"/>
				<api:set attr="out.orderNumber" 			value="[stopout.orderNumber | allownull()]"/>
				<api:set attr="out.products" 				value="[stopout.products | allownull()]"/>
				<api:set attr="out.telephoneNumber" 		value="[stopout.telephoneNumber | allownull()]"/>
				<api:set attr="out.tenantId" 				value="[stopout.tenantId | allownull()]"/>
				<api:push item="out"/>
			</api:call>
		</api:call>
	</api:script>
</api:script>

This is how you could do it. Only for you the baseurl would be https://www.energinet.net/api the first call is [urlbase]/unit to get the unit_id and then use it in a second call to get the [urlbase]/unitinfo/{unit_id}

A
Rank

It seems like the vendor is going to take it’s time to create new endpoints. In the mean time i have started using nested call to create a RSD-file. 

So far i have this, which workes fines: 

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">
	<!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. -->
	<api:info title="GetAllStops" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1">
    <!-- You can modify the name, type, and column size here. -->
    <attr name="date_from" 				  xs:type="string" readonly="false"              other:xPath="/json/datasources/available-data/date_from" />
    <attr name="date_to"   				  xs:type="string" readonly="false"              other:xPath="/json/datasources/available-data/date_to"   />
    <attr name="label"                    xs:type="string" readonly="false"              other:xPath="/json/datasources/label"                    />
    <attr name="name"                     xs:type="string" readonly="false"              other:xPath="/json/name"                                 />
    <attr name="file_id"                  xs:type="string" readonly="false"              other:xPath="/json/unit_id"                              />
  </api:info>

	<api:set attr="urlbase" 			    value="https://my-app/api/unit"/>
	<api:set attr="filein.DataModel" 		value="FLATTENEDDOCUMENTS" />
	<api:set attr="filein.JSONPath" 		value="$." /> 
	<api:set attr="filein.URITemplate" 		value="[urlbase]/20548folder"/>
	<api:set attr="filein.ElementMapPath#" 	value="/json/unit_id" />
	<api:set attr="filein.ElementMapName#" 	value="file_id" />

	<api:set attr="stopin.DataModel" 		value="FLATTENEDDOCUMENTS" />
	<api:set attr="stopin.EnablePaging" 	value="TRUE" />
	<api:set attr="stopin.JSONPath" 		value="$.;$.datasources;$.datasources.available-data" />
	<api:set attr="stopin.URITemplate" 		value="[urlbase]/file_id"/>

	<api:set attr="stopin.DataModel" 		value="FLATTENEDDOCUMENTS" />
	<api:set attr="stopin.EnablePaging" 	value="TRUE" />
	<api:set attr="stopin.JSONPath" 		value="$.;$.datasources;$.datasources.available-data" />
	<api:set attr="stopin.URITemplate" 		value="[urlbase]/file_id"/>



  <!-- The GET method corresponds to SELECT. Here you can override the default processing of the SELECT statement. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
	<api:script method="GET">
		<api:set attr="filein.URI" value="[filein.URITemplate]"/>
		<api:call op="jsonproviderGet" in="filein" out="fileout">
			<api:set attr="stopin.URI" value="[stopin.URITemplate | replace('file_id', [fileout.file_id])]"/>
			<api:call op="jsonproviderGet" in="stopin" out="stopout">
				<api:set attr="out.file_id" 						value="[stopout.file_id | allownull()]"/>
				<api:set attr="out.name" 							value="[stopout.name | allownull()]"/>
				<api:set attr="out.label" 							value="[stopout.label | allownull()]"/>
				<api:set attr="out.date_from" 						value="[stopout.date_from | allownull()]"/>
				<api:set attr="out.date_to" 						value="[stopout.date_to | allownull()]"/>
				<api:push item="out"/>
			</api:call>
		</api:call>
	</api:script>
</api:script>

Now, the next step is a bit tricky.

When i am at the end-point https://my-app/api/unit/file_id i have JSON structure like this:

[
    {
        "unit_id": "0000building",
        "name": "My building                ",
        },
        "datasources": [
            {
                "type": "1",
                "label": "Energy",
                "links": {
                    "data": {
                        "verb": "GET",
                        "href": "/api/unitdata/0000building1"
                    }
                },
                "available-data": {
                    "date_from": "2017-01-01T00:00:00+01:00",
                    "date_to": "2024-02-18T23:00:00+01:00"
                }
            },
            {
                "type": "2",
                "label": "Temperature",
                "links": {
                    "data": {
                        "verb": "GET",
                        "href": "/api/unitdata/0000building2"
                    }
                },
                "available-data": {
                    "date_from": "1998-01-02T00:00:00+01:00",
                    "date_to": "2024-02-19T01:00:00+01:00"
                }
            },
        ]

How do i append the trending digit inside (href://) the following:

<api:set attr="stopin.URI" value="[stopin.URITemplate | replace('file_id', [fileout.file_id])]"/>

unit_id the same for each herf

A
Rank

is it also possible to lopp through multiple endpoints like this:

<api:set attr="urlbase" 			    value="https://my-app.endpoint"/>
	<api:set attr="filein.DataModel" 		value="FLATTENEDDOCUMENTS" />
	<api:set attr="filein.JSONPath" 		value="$." /> 
	<api:set attr="filein.URITemplate" 		value="[urlbase]/api/unit/20548folder"/>
	<api:set attr="filein.ElementMapPath#" 	value="/json/unit_id" />
	<api:set attr="filein.ElementMapName#" 	value="unit_id" />

	<api:set attr="stopin.DataModel" 		value="FLATTENEDDOCUMENTS" />
	<api:set attr="stopin.EnablePaging" 	value="TRUE" />
	<api:set attr="stopin.JSONPath" 		value="$." />
	<api:set attr="stopin.URITemplate" 		value="[urlbase]/api/unit/unit_id"/>
	<api:set attr="filein.ElementMapPath#" 	value="/json/unit_id" />
	<api:set attr="filein.ElementMapName#" 	value="file_id0" />

	<api:set attr="stopin.DataModel" 		value="FLATTENEDDOCUMENTS" />
	<api:set attr="stopin.EnablePaging" 	value="TRUE" />
	<api:set attr="stopin.JSONPath" 		value="$." />
	<api:set attr="stopin.URITemplate" 		value="[urlbase]/api/unit/file_id0"/>
	<api:set attr="filein.ElementMapPath#" 	value="/json/unit_id" />
	<api:set attr="filein.ElementMapName#" 	value="file_id" />

 

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @aftabp 

Yes you can do multiple endpoints, each one just needs to have its own set of attributes.

That is also why I have stopin, stopout, filein and fileout. You need two for each additional call you want to make.

I got a old guide that shows this here. https://legacysupport.timextender.com/hc/en-us/articles/360052383191-Creating-and-using-RSD-files-for-CData-providers#2nestcall

You can point at href as a elementmapname# and elementmappath# and loop each href value with that.

You need to pull out the value and use it as the URI, not just a small replace, it needs to be the whole thing.

A
Rank

@Thomas Lind 
Just before you posted i did try href.

The code looks like this:

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">
	<!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. -->
	<api:info title="GetAllStops" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1">
    <!-- You can modify the name, type, and column size here. -->
	<attr name="End"             xs:type="string" readonly="false"              other:xPath="/json/End"             />
    <attr name="NanoSeconds"     xs:type="string" readonly="false"              other:xPath="/json/NanoSeconds"     />
    <attr name="Start"           xs:type="string" readonly="false"              other:xPath="/json/Start"           />
    <attr name="Value"           xs:type="string" readonly="false"              other:xPath="/json/Value"           />
	<attr name="IsValueEmpty"    xs:type="string" readonly="false"              other:xPath="/json/IsValueEmpty"    />
    </api:info>

	<api:set attr="urlbase" 			    value="https://my.app"/>
	<api:set attr="filein.DataModel" 		value="FLATTENEDDOCUMENTS" />
	<api:set attr="filein.JSONPath" 		value="$.;$.datasources" /> 
	<api:set attr="filein.URITemplate" 		value="[urlbase]/api/unit/0000folder"/>
	<api:set attr="filein.ElementMapPath#" 	value="/json/datasources/links/data/href" />
	<api:set attr="filein.ElementMapName#" 	value="href" />

	<api:set attr="stopin.DataModel" 		value="FLATTENEDDOCUMENTS" />
	<api:set attr="stopin.EnablePaging" 	value="TRUE" />
	<api:set attr="stopin.JSONPath" 		value="$." />
	<api:set attr="stopin.URITemplate" 		value="[urlbase]{href}"/>

  <!-- The GET method corresponds to SELECT. Here you can override the default processing of the SELECT statement. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
	<api:script method="GET">
		<api:set attr="filein.URI" value="[filein.URITemplate]"/>
		<api:call op="jsonproviderGet" in="filein" out="fileout">
			<api:set attr="stopin.URI" value="[stopin.URITemplate | replace('{href}', [fileout.href])]"/>
			<api:call op="jsonproviderGet" in="stopin" out="stopout">
				<api:set attr="out.Start" 				value="[stopout.Start 			| allownull()]"/>
				<api:set attr="out.End" 				value="[stopout.End 			| allownull()]"/>
				<api:set attr="out.NanoSeconds" 		value="[stopout.NanoSeconds 	| allownull()]"/>
				<api:set attr="out.Value" 				value="[stopout.Value 			| allownull()]"/>
				<api:set attr="out.IsValueEmpty" 		value="[stopout.IsValueEmpty 	| allownull()]"/>
				<api:push item="out"/>
			</api:call>
		</api:call>
	</api:script>
</api:script>


value for herf is :/api/v/0000building 

hence URITemplate is  [urlbase]{href} so that the URI wont get a double slash

Pagination is set to TURE

When i run it, it says

[500] Could not execute the specified command: 400 -- HTTP protocol error. 400 Bad Request.

 

However when i set pagination to FALSE i get retuned an empty table 

Dont know what i am doing wrong here 

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @aftabp 

I would usually check the log to see if it shows the path that gets generated. It usually explains it.

It should generate this Uri

https://my.app/api/unitdata/0000building1/

I don’t know if that is correct or not.

A
Rank

@Thomas Lind

When i have pagination set to TRUE and the code finds a building without any JSON structure , TX throws an error

2024-02-20T13:50:57.571+00:00    2    [137|Q-Id]    [HTTP|Req: 386] GET https://my.app/api/unitdata/0003building1
2024-02-20T13:50:57.571+00:00    3    [137|Q-Id]    [HTTP|Req: 386] [New]

 

So i guess, the issue lies in source data 

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @aftabp 

So the call is empty due to the source not existing?

I have seen that before, I can’t really remember if it was solved or how. I will see if I can find it.

The AllowNull() only works when it is a single or a few fields that are empty, not when it is all of them.

A
Rank

@Thomas Lind 

There aren’t any fields at all.

The endpoint in POSTMAN returns (just) null in body.

 

A
Rank

BTW

Is it possible to define somewhere in RSD to extract data from label Energy, since the following extract everything either its Energy or Temperature:

 

<api:set attr="filein.ElementMapPath#" value="/json/datasources/links/data/href" />

{
"unit_id": "0000building",
"name": "My Building",
 "datasources": [
            {
                "type": "1",
                "label": "Energy",
                "links": {
                    "data": {
                        "verb": "GET",
                        "href": "/api/unitdata/0000building1"
                    }
                },
            },
            {
                "type": "2",
                "label": "Temperature",
                "links": {
                    "data": {
                        "verb": "GET",
                        "href": "/api/unitdata/0000building2"
                    }
                },
            }
 ]
    }

 

Reply


Terms and ConditionsCookie settings