2

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:

  1. Pointers to flatten out dynamic JSON within SQL Server
  2. 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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dav.id
  • 2,757
  • 3
  • 45
  • 57

1 Answers1

1

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

enter image description here

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • 1
    If the analysis needs can be solved by this type of key/value pair table then this will be the most straightforward approach as the cube need not change when a new dynamic key arrives. However in my experience it is common for a report to need to do “and” filters such as “source=Bob Inc. AND name=Whips”. With a key/value table that’s not easy since a multi select “Bob Inc, Whips” means “Bob Inc OR Whips”. So if you need complex AND filters then you may have to use Tabular Object Model (TOM) to automate the creation of a new column in SSAS when a new key shows up in JSON. It’s possible but hard – GregGalloway Oct 23 '19 at 01:41