Cannot I use the outcome of a json serialize in a aggregate

Hello, 


I have a json file where somewhere a person is with the title of director.

So I thought I can fetch it .. serialize it and then use a aggreate

But it looks like I cannot put the outcome of the serialize into the aggerate.

Do I do something wrong or is the way I think wrong. 

Hi Roelof,

You should not serialize the structure to json.

You can access the properties from the stucture directly in your aggregate filter.

Regards,

Daniel

@Daniël: I think Roelof means deserializing.

@Roelof: No, Aggregates generate database queries, and since your record list is not in the database (but in Server memory), you cannot query them. There's no equivalent to .NET Linq in OutSystems.

The closest you can come is to use the List Actions like ListFilter, that allows you to retrieve certain elements from a List based on a condition. Note that it runs in Order(N) though, so it doesn't perform too well on very large lists.

Hi Roelof,

You are building a Reactive web application, and retrieve your data with an Data Action.

In the response of the data action is a Results list.

You can  add to your screen a list widget. Then drag the Results structure form the data action response in the list widget, This will schafold a listitem widget and show all data from the response. You don not need an aggregate for this.

Regarding filtering the displayed list you have the means to use list actions like Kilian already explained.

Regards,

Daniel

oke,


This is becoming a bigger mess then i thought. What I wanted to try to to display the director, producer and crew of a film which I asked of the movie database.


First thing I have to figure out now it what should be in the json string part and what is then the data-type. Do I have to deseralize every part of the json. I was hoping that I could deserialize the whole respons at once. 


Second problem what I suddenly see is reducancy in the database. When a actor plays in more then 1 movie,he/she will  be placed more then 1 in the local database. which is not good. So I have to  figure out how to make that work so there will be a table with films , actors and a  join table or maybe do not use a local database at all. 

Hi Roelof,

I'm unfortunately at a bit of a loss as to what you're saying. If I'm not mistaken, the movie database exposes a REST API you are consuming. If so, I don't see at all where you would have a "JSON string" and why you need to manually deserialize things. If you define the right Structures, the Platform does this for you automatically. Did you check the documentation about consuming REST Services? Is there anything not clear you'd like to have explained?

As for the second problem, whether you should go for complete rationalisation of your local database, I'd say it depends on the volume of redundant data and what you'd like to query on. In this specific case, yes, I'd say having a seperate Actor Entity would make sense, but then again, why do you want to store this data at all, if you are querying via a REST Service?

oke,


Some questions that came when I try to make this happens. I also think after a good walk I do not need a local database. 


For the first one I will try to explain better 


I ask the api for some data. There is no problem.

then I get back a json respons who looks like this :

 "crew": [
    {
      "credit_id": "589d3156c3a3685f9a001cd1",
      "department": "Directing",
      "gender": 2,
      "id": 1754586,
      "job": "Director",
      "name": "Miguel A. Ferrer",
      "profile_path": null
    },
    {
      "credit_id": "589d3166c3a3685f9a001ce3",
      "department": "Writing",
      "gender": 0,
      "id": 1754588,
      "job": "Writer",
      "name": "Anastazja Davis",
      "profile_path": null
    },
    {
      "credit_id": "589d3196c3a3685f8e001b83",
      "department": "Writing",
      "gender": 2,
      "id": 1754586,
      "job": "Writer",
      "name": "Miguel A. Ferrer",
      "profile_path": null
    },


What  I try to achieve now I that I find the person who is the director so I can place it on the site im building. 


and later on  I would do the same for the producer 


and under it the actors with the name and the role. 


The problem is that I do not see how to filter out the person which is for example the director. 

I cannot use a aggrete because im not working with the database. 

Solution

Hi Roelof,

The response of the "GetCredits" API method that you're calling contains a "Crew" attribute, which is a list. So in order to find the item in the list that corresponds, for instance, to the director, you're going to have to use the "LilstFilter" system action:



The "ListFilter" action has a "FilteredList" output parameter that contains the result of your search within the list. Now you're going to have to decide how you want to use that information. One possible approach would be to add a new output parameter to your data action and assign to it the result of the "ListFilter" action:





Solution

Does not work as I expect. I cannot display the name(s) of the directors on my page. 


maybe lowcode is not my cup of tea or I think of projects that are more then I can chew. 

Hi Roelof,

You're trying to display a single Record in a Table Records, that won't work.

Hi Roelof,

As Killian said, you don't need a table to display a single record. Now if you wanted, for instance, to display the movie's full cast, then you would need a table. But in that case, the data action's output parameter would have to be of the List type.

I'm attaching your OML with some changes I made showing one way of implementing this.

Thanks all., I quit with this one.

I try to do the same with the producers , where  I checked I have 3 and it seems that the table is falling out the main content because it's visible when starting and its stays empty after I entered a film 


I get more and more the feeling that outsystems is holding me back on making my ideas work instead of helping me so I can make things at a small time what for me RAD is 

Hi Roelof,

You cannot expect to be able to succesfully use a tool, even a RAD tool, if you're not willing to put in the time to learn how to use it. I get the feeling you want to be able to create everything that pops into your head without investing some time to follow the tutorials, take the courses and read the documentation. I'm sorry, but there's no tool you'll ever find that allows you to do this.

If you are really interested in working with OutSystems, my advise is to start with the various courses. One of these even guides you how to connect to the movie database and show the information in screen. It'll teach you about fetching data from REST services, display data in Table Records and Forms, refresh the Screen when changing data, and so on.

I did the web, mobile and the reactve course and all the challenges and quizzes so I tought I had enough knowlegde. But I also see that on all those courses only simple cases are explained. 


If you asked me to make the same work but then with user input, I think I can do it in a few days and maybe in one afternoon.  But the cases I wanted to try as more complex then any video that I saw so I do not complete agree with you that I did not have spend time to learn the basics. 


But what I said maybe I have made myself to  hard and have to try to make a simpler one where I only catch the poster on the front page and let the user enter all the data. 

but that does not feel right because most of the data is known at the api


or maybe reactieve is a step to far and I have to try to make it work as a tradional web app and first get more experience with that and then make the transaction to reactive. . 

Indeed. So I'd advise you to continue with your current design, it doesn't seem too difficult, and keep asking us questions about the stuff you're running into.

oke, surprising answer.  My last question was why the producers keep on being empty and outside the container which  had to take care that its hidden when there is no film entered. 


My orginal idea was to make a nicer layout for the idbm challenge I did for the tradional web developer course but I think im going to wild with ideas when I saw that most of the data you had to enter there are already avaible by the api. 

Hi Roelof,

First, I'd encourage you to debug the application if you haven't done already. The producers being empty can be a problem with fetching the data, filtering it, and assigining it to the Table Records. Also, if there's different user input, and you want to update things, you need to do Ajax Refreshes (or reload the entire screen) to make the changes visible.

also with reactive design. I did not do that on the other parts. 

The producers part was outside the container so that part is solved. 

I hope I can find out why the producers part is still empty. 


Condition is :  

SyntaxEditor Code Snippet

job = "Producer" 


where in the json I see this :

"credit_id": "5ab03c6fc3a36835f0000b89",
      "department": "Production",
      "gender": 2,
      "id": 191430,
      "job": "Producer",
      "name": "Marklen Kennedy",
      "profile_path": "/4K6vpwIa129L8EMU4eWGq1D1ZOr.jpg"


time for supper here and maybe I see then why its still empty what does not make any sense to me 

I assume that "job = "Producer"" is the condition for the ListFilter? Did you check the output of ListFilter? Does it contain a list of producers?

Yes, it does. 

So the problem must be in the assign after that.

I assume you have a Local Variable that's a List of the same type, and assign the output of the ListFilter to that Variable? And the Variable is the Source List of the Table Records?

time for a break.

Now I did thought I change so it works and now the whole producers table is not visible at all.


Roelof

If it's not visible at all, you may have an If Widget around it that hides it if a certain condition is True?

yep  I have but it the same condition as the poster and the producer. Very confused now 


Well, the condition should be something like "not ProducerList.Empty"?

I have a condition that the whole container is not visible if there is no title entered. 

Further I do not have any condition elsewhere

I cannot further do to this error message : 


Invalid Data Type

'CastItem List' data type required instead of 'CastItem'.


Which makes no sense because there are 3 items so it should be a list. 



it seems to work but it takes now a long long time that a user sees the page.


any hints why ? 

Roelof Wobben wrote:

it seems to work but it takes now a long long time that a user sees the page.


any hints why ? 

Tried installing your application but I can't see any performance issue on my side. Your network maybe?


oke,

then I will try it again

and maybe tommorow make a pagination on the crew . Sometimes there are a lot of names.


Someone else feedback on my first reactive app ? 

Kilian Hekhuis wrote:

@Roelof: No, Aggregates generate database queries, and since your record list is not in the database (but in Server memory), you cannot query them. There's no equivalent to .NET Linq in OutSystems.

The closest you can come is to use the List Actions like ListFilter, that allows you to retrieve certain elements from a List based on a condition. Note that it runs in Order(N) though, so it doesn't perform too well on very large lists.

I came here to comment that with SQL server, you can combine results from JSON with data from your database tables by using Advanced SQL widget. Then I noticed that Personal Environment databases are at compatibility level 120 instead of 130, which is needed to use OPENJSON() function in SQL clauses.

Query: Select name, compatibility_level from sys.databases produces output:

On quick try, I did not manage to change my personal environment's database to have compatibility level 130, but maybe there's a way to do so.

When this issue with compatibility level is sorted out (think external databases, on-premises, cloud installations or pretty please call to OS support) here's the trick: 

  1. create structure from JSON
  2. create advanced sql with original JSON as input and this freshly created structure as output
  3. select/filter/join data from this JSON structure as you wish

Here's Microsoft documentation on how to query JSON: https://docs.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server?view=sql-server-2017

More info from here: https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-2017

Interesting, thanks. I'd be curious to know though what the performance is, since JSON isn't indexed.