Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

boreus-rz
New Contributor

join on JSON extract

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) "

Tags (2)
6 Replies
erik_wetterberg
Honored Contributor

Re: join on JSON extract

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

Erik Wetterberg
https://extendingqlik.upper88.com/
boreus-rz
New Contributor

Re: join on JSON extract

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.

erik_wetterberg
Honored Contributor

Re: join on JSON extract

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

Erik Wetterberg
https://extendingqlik.upper88.com/
boreus-rz
New Contributor

Re: join on JSON extract

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

erik_wetterberg
Honored Contributor

Re: join on JSON extract

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

Erik Wetterberg
https://extendingqlik.upper88.com/
boreus-rz
New Contributor

Re: join on JSON extract

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.

Community Browser