topic Re: join on JSON extract in App Development

join on JSON extract

boreus-rz — Tue, 17 Apr 2018 08:10:20 GMT

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

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

Re: join on JSON extract

ErikWetterberg — Tue, 17 Apr 2018 08:17:41 GMT

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

Re: join on JSON extract

boreus-rz — Tue, 17 Apr 2018 08:29:22 GMT

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.

Re: join on JSON extract

ErikWetterberg — Tue, 17 Apr 2018 08:38:53 GMT

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

Re: join on JSON extract

boreus-rz — Tue, 17 Apr 2018 08:41:07 GMT

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

Re: join on JSON extract

ErikWetterberg — Tue, 17 Apr 2018 08:57:56 GMT

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

Re: join on JSON extract

boreus-rz — Tue, 17 Apr 2018 09:06:20 GMT

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.