Hi,

It is maybe all obvious to you as you are looking at that thing for hours, but there isn't much context given.

What are you trying to do? Where? How? What tracking are you talking about?

PS: those names they look to me very well structured and easy to split to get just the right piece of info, therefore it sounds like something else is missing in your question to understand where the issue is.

Those extra info you see in the path is because a DV workbook is a bit the equivalent of a "dashboard": it has "pages" (canvases) and contains visualizations. Each single one generate different queries.

Therefore the path identify them in a unique way going deeper than just the workbook itself (a workbook could contains 100+ visualization generating queries, but if they are split in various pages only a fraction of them is accessed and executed and recorded in the usage tracking).

Just do a simple string manipulation to get the workbook path part and ignore the rest.

In what you posted, despite being truncated on the left, it looks like a the paths are very well separated from the rest: everything is quoted, just use that as rule to split the string.

How to make something very different fit into something old.

There are generally 2 ways to maintain a kind of backward compatibility: extend the existing to add the new pieces of info needed in new columns, maintaining the existing ("classic") behavior as much as possible, or force things to fit in what is available in a way or another.

That's how a column that was meant to be a string with the path of a catalog object turned into a JSON object containing more than that.

It isn't a fully wrong approach to use JSON, but consistency is required: turn the "classic" usage tracking into JSON objects as well, even if they just have the "path" key and nothing else (because in "classic" each piece executing a single query is stored independently in the catalog).

The current solution is a half-half that doesn't really satisfy anyone: it does break backward compatibility because queries from DV requires a different processing that queries from "classic", and therefore adding new columns just for DV would have been easier and cleaner. But maybe more complicated for upgrades because somebody has to run an ALTER statement on their database?

By the way, the cleaner solution for the usage tracking data would be a view in the database on top of the usage tracking table making a union between "classic" usage tracking rows and DV usage tracking rows extracting the JSON values into separate columns. The processing of the JSON shouldn't be done in a logical column in the RPD or in an analysis/workbook. The database speaks JSON perfectly, the view handling the job is the easier, safer and better "long term" approach (until the usage tracking data change structure).

with s_nq_acct_json as (
  select saw_src_path
  , treat(saw_src_path as json) as saw_src_path_json
  from s_nq_acct
  where query_src_cd = 'Visual Analyzer'
)
select s.saw_src_path_json
, s.saw_src_path_json.path
, s.saw_src_path_json.viewID
, s.saw_src_path_json.currentCanvas
from s_nq_acct_json s;