SQL Server dynamic JSON using within Analysis Services?

Question

I am trying to get my head around which direction to even start with the following..

Imaging a dynamic form (JSON) that I store in SQL Server 2016+. So far, I have seen / tried a couple of dynamic queries to take the dynamic JSON and flatten out as columns.

Given the "dynamic" nature, it is hard to "store" that flatten out data. I have been looking at temporary/temporal/memory tables to store that dynamic flattened data for a "relatively short period" of time (say an hour or two).

I have also been asked if it is possible to use the dynamic JSON data in building a cube within Analysis Services.. again given the dynamic nature of this, would something like this even be possible?

I guess my question is two-fold:

Pointers to flatten out dynamic JSON within SQL Server
Is it possible to take dynamic JSON, flatten out to columns and somehow use within Analysis Services? i.e. ultimately to use within a cube?

Realise the above is a bit vague, but any pointers to get me going in the correct direction would be appreciated!

Many thanks.

user_1570000 · Answer

Dynamically converting JSON into columns can get tricky. Especially if you are NOT certain of the structure. That said, have you considered converting the JSON into a hierarchy via a Recursive CTE?

Example

declare @json varchar(max)='
[
  {
    "url": "https://www.google.com",
    "image-url": "https://www.google.com/imghp",
    "labels": [
                {
                  "source": "Bob, Inc",
                  "name": "Whips",
                  "info": "Ouch"
                },
                {
                  "source": "Weezles of Oregon",
                  "name": "Chains",
                  "info": "Let me go"
                }
              ],
    "Fact": "Fictional"
  }
]';


;with cte0 as (
   Select *
         ,[Level]=1 
         ,[Path]=convert(varchar(max),row_number() over(order by (select null)))
    From OpenJSON(@json,'$') 
   Union All
   Select R.*
         ,[Level]=p.[Level]+1 
         ,[Path]=concat(P.[Path],'\',row_number() over(order by (select null)))
    From  cte0 p 
    Cross Apply OpenJSON(p.value,'$') R
    Where P.[Type]>3
)
Select [Level]
      ,[Path]
      ,Title = replicate('|---',[Level]-1)+[Key]
      ,Item  = [Key]
      ,Value = case when [type]<4 then Value else null end 
 From cte0
 Order By [Path]

Returns

SQL Server dynamic JSON using within Analysis Services?

1 Answers