OData: receiving JSON not mapping to BC Pages/Queries

Wisa123
Wisa123 Member Posts: 309
edited 2020-03-05 in NAV Three Tier
Hi experts,

Is there any best practice to receive and process a complex JSON Payload via http, without the need of it mapping to a BC Page/Query?

Example: 
{
    "title": "mibuso",
    "users": [
        {
            "name": "Wisa123",
            "property1": "one",
            "property2": "two",
            "posts": [
                {
                    "title":"OData: receiving JSON not mapping to BC Pages/Queries",
                    "answers":10,
                    "solved":true
                }
            ]
        },
        {
            "name": "test",
            "property1": "one",
            "property2": "two",
            "posts": [
                {
                    "title":"testing stuff",
                    "answers":10,
                    "solved":true
                }
            ]
        },
        ...
    ]
}

This is just some data I made up for the sake of the thread here. I need to create an endpoint that receives this document and for example creates Employees based on the usernames.

If API Pages could just have something like this things would be easy: 
local procedure Post(payload: JsonObject)
But since everything that is sent towards BC needs to comply with the format the Page/Query define things are tricky. Especially when complex Types and Arrays come into play. I've looked into EdmTypes and kinda managed to get something to work.

Sadly that integration ("Odata Edm Type") is undocumented, also there are barely any examples out there (vjeko did something on this, but not in the complexity I'm asking for).

Any help appreciated, I realize this is a very special request, and most people won't stumble across this in normal projects.



Austrian NAV/BC Dev

Answers

  • ftornero
    ftornero Member Posts: 524
    Hello @Wisa123

    Take a look to this post, I think that it could give you some ideas:

    https://www.kauffmann.nl/2020/03/05/codeunit-apis-in-business-central/

    Regards
  • Wisa123
    Wisa123 Member Posts: 309
    Hi,

    Thanks for that link @ftornero I actually didn't stumple upon that one while googling.
    However, even AJK mentions
    What about return complex types, like a Json payload? Unfortunately, that doesn’t work as you would like:
    ...
    The data is formatted as a Json text value instead of a real Json structure. And if you try to change the function to return a JsonObject rather than a text variable, then the whole web service is not valid anymore as a web service and you will not be able to call it. For this to work, we need an option to define custom entities and add it to the metadata. It would be great if Microsoft would enable this!

    Well the OdataEdmTypes are exactly this possibility to create custom entities.

    However these OdataEdmTypes seem to be limited to relatively simple/shallow JSON documents.
    By now i spent a lot (way too much) time trying to get the EdmTypes to work. I also created a GIthub issue regarding this.
    https://github.com/microsoft/ALAppExtensions/issues/6360

    I'll try to keep this thread updated in case something happens in this issue, so future people (and probably myself in a few years) can find this.

    Regards
    Austrian NAV/BC Dev
  • wait
    wait Member Posts: 53
    Wisa123 wrote: »
    Hi,

    Thanks for that link @ftornero I actually didn't stumple upon that one while googling.
    However, even AJK mentions
    What about return complex types, like a Json payload? Unfortunately, that doesn’t work as you would like:
    ...
    The data is formatted as a Json text value instead of a real Json structure. And if you try to change the function to return a JsonObject rather than a text variable, then the whole web service is not valid anymore as a web service and you will not be able to call it. For this to work, we need an option to define custom entities and add it to the metadata. It would be great if Microsoft would enable this!

    Well the OdataEdmTypes are exactly this possibility to create custom entities.

    However these OdataEdmTypes seem to be limited to relatively simple/shallow JSON documents.
    By now i spent a lot (way too much) time trying to get the EdmTypes to work. I also created a GIthub issue regarding this.
    https://github.com/microsoft/ALAppExtensions/issues/6360

    I'll try to keep this thread updated in case something happens in this issue, so future people (and probably myself in a few years) can find this.

    Regards

    I got really frustrated when I found out how limited this is and spent some time trying to work around this without success. Since then I have accepted that I'm limited to three levels, API Page/SubPage/Complex Type and sometimes I have also flattened the data to work with this limitation. It's nice move to create Github issue, hopefully it returns something.
  • ftornero
    ftornero Member Posts: 524
    Hello @Wisa123,

    Using how base the AJK code you can create this procedure in the codeunit to test the data back and forth of BC:
        procedure GetJsonData2(RequestValue: Text) ReturnValue: Text
    var
        Jobj: JsonObject;
    begin
        if JObj.ReadFrom(RequestValue) then
            Jobj.WriteTo(ReturnValue)
        else
            ReturnValue := 'Error converting';
    end;

    And with "REST Client" inside VSCode you can call that procedure:
    post http://bcim200305:7048/BC/ODataV4/NAV.MyUnboundActions_GetJsonData2
Authorization: Basic {{username}} {{password}}
Content-Type: application/json

{
    "requestValue": "{\"title\": \"mibuso\",
        \"users\":
            [{\"name\": \"Wisa123\",
            \"property1\": \"one\",
            \"property2\":\"two\", 
            \"posts\": [
                {
                    \"title\":\"OData: receiving JSON not mapping to BC Pages/Queries\",
                    \"answers\":10,
                    \"solved\":true
                }
            ]
        },
        {
            \"name\": \"test\",
            \"property1\": \"one\",
            \"property2\": \"two\",
            \"posts\": [
                {
                    \"title\":\"testing stuff\",
                    \"answers\":10,
                    \"solved\":true
                }
            ]
        }
        ]
    }"
}

    And you must get back the same JSON data you send.

    The backslashs are necesary for the "REST Client", I have an example with Powershell where you deal with the JSON data in a better way.

    Bottom line is you pass to BC a text parameter (RequestValue) with the JSON data and you can process it in BC and do whatever you need to do.

    Regards
  • Wisa123
    Wisa123 Member Posts: 309
    edited 2020-03-12
    Hi @ftornero,

    Thanks for the example and explanation.
    This obviously works, but there is still a problem.

    In order to work like this you need to have control over the sent/received JSON structure.
    In my example the JSON I described in the OP is the HTTP Payload, and there is no altering it since it comes from an external system.

    Since I can't tell the external system that POST's towards my BC to wrap everything up in a single JSON Object this won't work out like this. Still looking for a solution to this. This is what an incoming POST would look like, no chance to alter it:
    post http://bcim200305:7048/BC/ODataV4/NAV.MyUnboundActions_GetJsonData2
Authorization: Basic {{username}} {{password}}
Content-Type: application/json

{
    "title": "mibuso",
    "users": [
        {
            "name": "Wisa123",
            "property1": "one",
            "property2": "two",
            "posts": [
                {
                    "title":"OData: receiving JSON not mapping to BC Pages/Queries",
                    "answers":10,
                    "solved":true
                }
            ]
        },
        {
            "name": "test",
            "property1": "one",
            "property2": "two",
            "posts": [
                {
                    "title":"testing stuff",
                    "answers":10,
                    "solved":true
                }
            ]
        },
    ]
}

    Austrian NAV/BC Dev
  • ftornero
    ftornero Member Posts: 524
    Hello @Wisa123,

    Maybe I am missing something, but when an external system want to connect to you the external system must to comply with your specifications.

    But anyway, in this case you will need to create an Azure Function that get the POST JSON from the external system a wrap it the way you need it and pass forward to your BC system.

    Regards
  • Wisa123
    Wisa123 Member Posts: 309
    Hi,

    Yeah I kinda thought this would be the end result - sadly.
    the external system must to comply with your specifications
    Well for the other side BC is the external system :#

    Would've been nice to do it in full AL without bothering with an additional proxy component.

    anyway, thanks a lot for your help and time @ftornero.

    Regards
    Austrian NAV/BC Dev
  • wait
    wait Member Posts: 53
    Wisa123 wrote: »
    Hi,

    Thanks for that link @ftornero I actually didn't stumple upon that one while googling.
    However, even AJK mentions
    What about return complex types, like a Json payload? Unfortunately, that doesn’t work as you would like:
    ...
    The data is formatted as a Json text value instead of a real Json structure. And if you try to change the function to return a JsonObject rather than a text variable, then the whole web service is not valid anymore as a web service and you will not be able to call it. For this to work, we need an option to define custom entities and add it to the metadata. It would be great if Microsoft would enable this!

    Well the OdataEdmTypes are exactly this possibility to create custom entities.

    However these OdataEdmTypes seem to be limited to relatively simple/shallow JSON documents.
    By now i spent a lot (way too much) time trying to get the EdmTypes to work. I also created a GIthub issue regarding this.
    https://github.com/microsoft/ALAppExtensions/issues/6360

    I'll try to keep this thread updated in case something happens in this issue, so future people (and probably myself in a few years) can find this.

    Regards

    I got really frustrated when I found out this limitation and spent some time trying to work around it without any luck. Since then I have accepted that I'm limited with three level API Page/SubPage/Complex Type, and sometimes I have also flattened data because of this. It's a nice move to create a ticket for this, I'm going to follow this thread ;)
  • wait
    wait Member Posts: 53
    Wisa123 wrote: »
    Hi,

    Thanks for that link @ftornero I actually didn't stumple upon that one while googling.
    However, even AJK mentions
    What about return complex types, like a Json payload? Unfortunately, that doesn’t work as you would like:
    ...
    The data is formatted as a Json text value instead of a real Json structure. And if you try to change the function to return a JsonObject rather than a text variable, then the whole web service is not valid anymore as a web service and you will not be able to call it. For this to work, we need an option to define custom entities and add it to the metadata. It would be great if Microsoft would enable this!

    Well the OdataEdmTypes are exactly this possibility to create custom entities.

    However these OdataEdmTypes seem to be limited to relatively simple/shallow JSON documents.
    By now i spent a lot (way too much) time trying to get the EdmTypes to work. I also created a GIthub issue regarding this.
    https://github.com/microsoft/ALAppExtensions/issues/6360

    I'll try to keep this thread updated in case something happens in this issue, so future people (and probably myself in a few years) can find this.

    Regards

    I got really frustrated when I found out this limitation and spent some time trying to work around it without any luck. Since then I have accepted that I'm limited with three level API Page/SubPage/Complex Type, and sometimes I have also flattened data because of this. It's a nice move to create a ticket for this, I'm going to follow this thread ;)
  • wait
    wait Member Posts: 53
    Wisa123 wrote: »
    Hi,

    Thanks for that link @ftornero I actually didn't stumple upon that one while googling.
    However, even AJK mentions
    What about return complex types, like a Json payload? Unfortunately, that doesn’t work as you would like:
    ...
    The data is formatted as a Json text value instead of a real Json structure. And if you try to change the function to return a JsonObject rather than a text variable, then the whole web service is not valid anymore as a web service and you will not be able to call it. For this to work, we need an option to define custom entities and add it to the metadata. It would be great if Microsoft would enable this!

    Well the OdataEdmTypes are exactly this possibility to create custom entities.

    However these OdataEdmTypes seem to be limited to relatively simple/shallow JSON documents.
    By now i spent a lot (way too much) time trying to get the EdmTypes to work. I also created a GIthub issue regarding this.
    https://github.com/microsoft/ALAppExtensions/issues/6360

    I'll try to keep this thread updated in case something happens in this issue, so future people (and probably myself in a few years) can find this.

    Regards

    I got really frustrated when I found out this limitation and spent some time trying to work around it without any luck. Since then I have accepted that I'm limited with three level API Page/SubPage/Complex Type, and sometimes I have also flattened data because of this. It's a nice move to create a ticket for this, I'm going to follow this thread ;)
  • wait
    wait Member Posts: 53
    Wisa123 wrote: »
    Hi,

    Thanks for that link @ftornero I actually didn't stumple upon that one while googling.
    However, even AJK mentions
    What about return complex types, like a Json payload? Unfortunately, that doesn’t work as you would like:
    ...
    The data is formatted as a Json text value instead of a real Json structure. And if you try to change the function to return a JsonObject rather than a text variable, then the whole web service is not valid anymore as a web service and you will not be able to call it. For this to work, we need an option to define custom entities and add it to the metadata. It would be great if Microsoft would enable this!

    Well the OdataEdmTypes are exactly this possibility to create custom entities.

    However these OdataEdmTypes seem to be limited to relatively simple/shallow JSON documents.
    By now i spent a lot (way too much) time trying to get the EdmTypes to work. I also created a GIthub issue regarding this.
    https://github.com/microsoft/ALAppExtensions/issues/6360

    I'll try to keep this thread updated in case something happens in this issue, so future people (and probably myself in a few years) can find this.

    Regards

    I got really frustrated when I found out this limitation and spent some time trying to work around it without any luck. Since then I have accepted that I'm limited with three level API Page/SubPage/Complex Type, and sometimes I have also flattened data because of this. It's a nice move to create a ticket for this, I'm going to follow this thread ;)
  • wait
    wait Member Posts: 53
    Wisa123 wrote: »
    Hi,

    Thanks for that link @ftornero I actually didn't stumple upon that one while googling.
    However, even AJK mentions
    What about return complex types, like a Json payload? Unfortunately, that doesn’t work as you would like:
    ...
    The data is formatted as a Json text value instead of a real Json structure. And if you try to change the function to return a JsonObject rather than a text variable, then the whole web service is not valid anymore as a web service and you will not be able to call it. For this to work, we need an option to define custom entities and add it to the metadata. It would be great if Microsoft would enable this!

    Well the OdataEdmTypes are exactly this possibility to create custom entities.

    However these OdataEdmTypes seem to be limited to relatively simple/shallow JSON documents.
    By now i spent a lot (way too much) time trying to get the EdmTypes to work. I also created a GIthub issue regarding this.
    https://github.com/microsoft/ALAppExtensions/issues/6360

    I'll try to keep this thread updated in case something happens in this issue, so future people (and probably myself in a few years) can find this.

    Regards

    I got really frustrated when I found out this limitation and spent some time trying to work around it without any luck. Since then I have accepted that I'm limited with three level API Page/SubPage/Complex Type, and sometimes I have also flattened data because of this. It's a nice move to create a ticket for this, let's hope it returns something.
  • wait
    wait Member Posts: 53
    Wisa123 wrote: »
    Hi,

    Thanks for that link @ftornero I actually didn't stumple upon that one while googling.
    However, even AJK mentions
    What about return complex types, like a Json payload? Unfortunately, that doesn’t work as you would like:
    ...
    The data is formatted as a Json text value instead of a real Json structure. And if you try to change the function to return a JsonObject rather than a text variable, then the whole web service is not valid anymore as a web service and you will not be able to call it. For this to work, we need an option to define custom entities and add it to the metadata. It would be great if Microsoft would enable this!

    Well the OdataEdmTypes are exactly this possibility to create custom entities.

    However these OdataEdmTypes seem to be limited to relatively simple/shallow JSON documents.
    By now i spent a lot (way too much) time trying to get the EdmTypes to work. I also created a GIthub issue regarding this.
    https://github.com/microsoft/ALAppExtensions/issues/6360

    I'll try to keep this thread updated in case something happens in this issue, so future people (and probably myself in a few years) can find this.

    Regards

    I got really frustrated when I found out this limitation and spent some time trying to work around it without any luck. Since then I have accepted that I'm limited with three level API Page/SubPage/Complex Type, and sometimes I have also flattened data because of this. It's a nice move to create a ticket for this, let's hope it returns something.
  • wait
    wait Member Posts: 53
    Wisa123 wrote: »
    Hi,

    Thanks for that link @ftornero I actually didn't stumple upon that one while googling.
    However, even AJK mentions
    What about return complex types, like a Json payload? Unfortunately, that doesn’t work as you would like:
    ...
    The data is formatted as a Json text value instead of a real Json structure. And if you try to change the function to return a JsonObject rather than a text variable, then the whole web service is not valid anymore as a web service and you will not be able to call it. For this to work, we need an option to define custom entities and add it to the metadata. It would be great if Microsoft would enable this!

    Well the OdataEdmTypes are exactly this possibility to create custom entities.

    However these OdataEdmTypes seem to be limited to relatively simple/shallow JSON documents.
    By now i spent a lot (way too much) time trying to get the EdmTypes to work. I also created a GIthub issue regarding this.
    https://github.com/microsoft/ALAppExtensions/issues/6360

    I'll try to keep this thread updated in case something happens in this issue, so future people (and probably myself in a few years) can find this.

    Regards

    I got really frustrated when I found out this limitation and spent some time trying to work around it without any luck. Since then I have accepted that I'm limited with three level API Page/SubPage/Complex Type, and sometimes I have also flattened data because of this. It's a nice move to create a ticket for this, I'm going to follow this thread ;)
  • wait
    wait Member Posts: 53
    Wisa123 wrote: »
    Hi,

    Thanks for that link @ftornero I actually didn't stumple upon that one while googling.
    However, even AJK mentions
    What about return complex types, like a Json payload? Unfortunately, that doesn’t work as you would like:
    ...
    The data is formatted as a Json text value instead of a real Json structure. And if you try to change the function to return a JsonObject rather than a text variable, then the whole web service is not valid anymore as a web service and you will not be able to call it. For this to work, we need an option to define custom entities and add it to the metadata. It would be great if Microsoft would enable this!

    Well the OdataEdmTypes are exactly this possibility to create custom entities.

    However these OdataEdmTypes seem to be limited to relatively simple/shallow JSON documents.
    By now i spent a lot (way too much) time trying to get the EdmTypes to work. I also created a GIthub issue regarding this.
    https://github.com/microsoft/ALAppExtensions/issues/6360

    I'll try to keep this thread updated in case something happens in this issue, so future people (and probably myself in a few years) can find this.

    Regards

    I got really frustrated when I found out this limitation and spent some time trying to work around it without any luck. Since then I have accepted that I'm limited with three level API Page/SubPage/Complex Type, and sometimes I have also flattened data because of this. It's a nice move to create a ticket for this, I'm going to follow this thread ;)
  • wait
    wait Member Posts: 53
    Wisa123 wrote: »
    Hi,

    Thanks for that link @ftornero I actually didn't stumple upon that one while googling.
    However, even AJK mentions
    What about return complex types, like a Json payload? Unfortunately, that doesn’t work as you would like:
    ...
    The data is formatted as a Json text value instead of a real Json structure. And if you try to change the function to return a JsonObject rather than a text variable, then the whole web service is not valid anymore as a web service and you will not be able to call it. For this to work, we need an option to define custom entities and add it to the metadata. It would be great if Microsoft would enable this!

    Well the OdataEdmTypes are exactly this possibility to create custom entities.

    However these OdataEdmTypes seem to be limited to relatively simple/shallow JSON documents.
    By now i spent a lot (way too much) time trying to get the EdmTypes to work. I also created a GIthub issue regarding this.
    https://github.com/microsoft/ALAppExtensions/issues/6360

    I'll try to keep this thread updated in case something happens in this issue, so future people (and probably myself in a few years) can find this.

    Regards

    I got really frustrated when I found out this limitation and spent some time trying to work around it without any luck. Since then I have accepted that I'm limited with three level API Page/SubPage/Complex Type, and sometimes I have also flattened data because of this. It's a nice move to create a ticket for this, I'm going to follow this thread ;)
  • wait
    wait Member Posts: 53
    Wisa123 wrote: »
    Hi,

    Thanks for that link @ftornero I actually didn't stumple upon that one while googling.
    However, even AJK mentions
    What about return complex types, like a Json payload? Unfortunately, that doesn’t work as you would like:
    ...
    The data is formatted as a Json text value instead of a real Json structure. And if you try to change the function to return a JsonObject rather than a text variable, then the whole web service is not valid anymore as a web service and you will not be able to call it. For this to work, we need an option to define custom entities and add it to the metadata. It would be great if Microsoft would enable this!

    Well the OdataEdmTypes are exactly this possibility to create custom entities.

    However these OdataEdmTypes seem to be limited to relatively simple/shallow JSON documents.
    By now i spent a lot (way too much) time trying to get the EdmTypes to work. I also created a GIthub issue regarding this.
    https://github.com/microsoft/ALAppExtensions/issues/6360

    I'll try to keep this thread updated in case something happens in this issue, so future people (and probably myself in a few years) can find this.

    Regards

    I got really frustrated when I found out this limitation and spent some time trying to work around it without any luck. Since then I have accepted that I'm limited with three level API Page/SubPage/Complex Type, and sometimes I have also flattened data because of this. It's a nice move to create a ticket for this, I'm going to follow this thread ;)

Categories