6 Replies Latest reply: Apr 17, 2018 5:06 AM by Frank Schoch RSS

    join on JSON extract

    Frank Schoch

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