ODATA: Function that returns nested JSON

Hello Bas,

Have a look at my reply here. This is what you're looking for.

I hope this helps.

Hi Vincent,

I had looked into that post before I made this one.

The `$expand=` option was promissing, but further experimentation made clear that that can't be classified as low-code. It would be very complicated for an external party to write an interface to our API that way. And besides that, I feel I do not have control over the resulting JSON document. For example, when using `$expand=detail_..` I have no control over how it is put in the JSON message (can't use an ALIAS).

The other option, as detailed in the blog post, can be thing to have a closer look at. But the fact that I then need to supply all features that come with OData, myself, makes it over complicated. 

Then, there is the table-valued-function. This lacks the ability to create a structured (nested) JSON message.

Thinkwise almost does what I, and others need: return a JSON message that results from a query like

select ...
  from ...
  join ...
    on ...
   for JSON auto

 instead of a string of characters that get escaped by some layer in between the SQL function and the OData result.

@BasWesselink you can create the json but return it Table valued in stead of a scalar. That would return the json unescaped.

Does the table-valued-function support nested data? Like 

{                                                
    "orders": [                                  
        {                                        
            "id": 1,                             
            "customer_id": 1,                    
            "order_date": "2023-06-31",          
            "delivery_address": {                
                "street_1": "street",            
                "street_2": null                 
            },                                   
            "order_lines": [                     
                {                                
                    "id": 1,                     
                    "product_id": 1,             
                    "price": 10.99               
                },                               
                {                                
                    "id": 2,                     
                    "product_id": 12,            
                    "price": 10.99               
                },                               
                {                                
                    "id": 3,                     
                    "product_id": 13,            
                    "price": 10.99               
                },                               
                {                                
                    "id": 4,                     
                    "product_id": 14,            
                    "price": 10.99               
                }                                
            ]                                    
        },                                       
        {                                        
            "id": "and so on"                    
        }                                        
    ]                                            
}                                                

 instead of a list rows, like Excel?

Does the table-valued-function support nested data?

No, it does not. A view and then expanding references on that view is richer in that sense, but as you mentioned, you can't provide an alias for these expanded references.

At this moment, the only option that meets all of your requirements (regarding the output) is the one that I linked before, using a Message Protocol Independent process flow

However, as you stated yourself, if you want to support the various OData operations such as $filter and $orderby as well, then I agree that this option is not ideal either. It should be noted that you are no longer tied to OData, so instead of ?$filter=id eq 123 you could also opt for ?id=123, which is rather simple to parse, but limited in functionality.

If this option does not suffice, then I would recommend creating an idea for the solution that you would like to see.

@BasWesselink what I was aiming at was:

return

select *

  from ( query that creates your json ) j

 Should return it as a json (unescaped)..  You might have to do direct $select=<column> 

where the selected column is the full json .. so the TVF just returns one row and one column..  

it's a bit of a hack.. but if I remember correctly this should function..  

select *

  from ( query that creates your json ) j

 Should return it as a json (unescaped)..  You might have to do direct $select=<column> 

where the selected column is the full json .. so the TVF just returns one row and one column..  

it's a bit of a hack.. but if I remember correctly this should function..  

This cannot work, because Indicium returns JSON responses itself. The produced JSON will have to be returned in a (n)varchar column, which would be a string property in Indicium's own JSON response. A JSON string cannot contain unescaped quotation marks.

What you're suggesting would return this:

{
   "value": [
      {
         "column": "<JSON value>"
      }
   ]
}

The problem is the quotation marks surrounding <JSON value>, these cause the value of “column” to be a JSON string, which causes any quotation marks inside of <JSON value> to be escaped. But Indicium is correct in serializing the value of “column” as a JSON string, because “column” is defined in the SF to be a string type (i.e. (n)varchar). The fact that this string value could be parsed as JSON is not something that Indicium knows and Indicium has no method of finding this out except for attempting to parse every string value as JSON and serializing it ‘as is’ on success, which is not viable.

I'm elaborating on this because this problem is often being framed incorrectly. The focus is on “Indicium escapes my JSON”, but that's not the real issue. Indicium is escaping quotation marks in your string which would otherwise have terminated your string prematurely. The issue is that there isn't a way to return JSON in the first place. The database server doesn't even allow JSON columns or parameters, there are only (n)varchars and some of them might contain valid JSON. So what is really being asked is if Indicium can not serialize strings as strings.

So a potential solution is the ability to mark a domain as JSON somehow. Another solution is to be able to control the alias of expanded detail_ references and such, which would allow a small amount of influence on Indicium's regular responses in order for them to conform to certain API specs. The second solution is a bit less versatile, but preferable over the first solution, because it does not leave room for errors and it doesn't require Indicium to not serialize strings as strings.

I hope this explains a few things.