join on JSON extract

[boreus-rz]

hello, I have a question about an ugly situation. I load my data via SQL from a MariaDB. Various tables are connected by "join". A table contains a JSON in which, among other things, an id can be found. If I link an id to "id" from the JSON in the "on" of this "join", Qlik Sense will need 20GB more RAM when saving the app than extracting the "id" from the JSON via "regexp_substr". After the data has been loaded, the app itself is also very inexperienced and the engine can hardly handle the data.
Do you know this situation or can you explain how this happens?
I thank you.

Bad way: " on p.id = json_value (dph.data, '$ .responsible') "

vs

Good way: " on p.id = if((@pid := regexp_replace(regexp_substr(dph.data, '"responsible":"{0,1} d+'), '^"responsible":"{0,1}(d+)', '1')) != '', @pid, null) "

[ErikWetterberg]

Hi,

This feels more like a Maria DB problem, but anyhow: what does the LOAD staement look like. Do you do anything like LOAD * ?

Erik Wetterberg

[boreus-rz]

no, no load. just a sql statement.

I suspect that mariadb could be guilty too. Only I wanted to ask here first if this ever schonmal noticed someone, or if this can explain someone here.

[ErikWetterberg]

Hi,

If you have no LOAD then try adding one where you list just the fields you actually want to have in your app. It should be before the select.

Erik Wetterberg

[boreus-rz]

Hello, why should I do that? Or what's the difference?
VG

[ErikWetterberg]

If the problem is that Maria DB returns more data than you want, that's a way to exclude it (or only include what you really want).

Erik Wetterberg

[boreus-rz]

ok, I've nachgelsene to the subject what that makes.
Now I've tried this in a test app. Strangely enough, the unperforming script runs through without any problems.