How to build a Multi-Active Satellite table from a table containing json query?

Question

I have a table like follow:

id          |first_active           |openingtimes_json
8326cdd20459|1970-01-01 01:00:00+01 |{"openingTimes":[{"applicable_days":63,"periods":[{"startp":"06:00","endp":"22:00"}]},{"applicable_days":64,"periods":[{"startp":"07:00","endp":"21:00"}]}]}
d392f7532218|1970-01-01 01:00:00+01 |{"openingTimes":[{"applicable_days":31,"periods":[{"startp":"06:00","endp":"22:00"}]},{"applicable_days":64,"periods":[{"startp":"09:00","endp":"22:00"}]},{"applicable_days":32,"periods":[{"startp":"08:00","endp":"22:00"}]}]}

I want to have a Satellite table according to Data Vault principle like this:

id           |subsq|first_active           |applicable_days|startp |endp  |
8326cdd20459 |1    |1970-01-01 01:00:00+01 |63             |06:00  |22:00 |
8326cdd20459 |2    |1970-01-01 01:00:00+01 |64             |07:00  |21:00 |
d392f7532218 |1    |1970-01-01 01:00:00+01 |31             |06:00  |22:00 |
d392f7532218 |2    |1970-01-01 01:00:00+01 |64             |09:00  |22:00 |
d392f7532218 |3    |1970-01-01 01:00:00+01 |32             |08:00  |22:00 |

By now I only know how to select the content of the json query. For example if I run JSON_VALUE([openingtimes_json], '$.openingTimes[0}.applicable_days')

I get 63 for the first record.

Answer 1

You need SQL Server 2016+ to parse the stored JSON using two OPENJSON() calls (with default and explicit schema) and the appropriate APPLY operator.

As a note, I assume, that $.periods JSON array has one item (if not, an additional APPLY operator and an OPENJSON() call are needed):

SELECT 
   v.id, 
   CONVERT(int, j1.[key]) + 1 AS subsq,
   v.first_active,
   j2.applicable_days, 
   j2.startp, 
   j2.endp
FROM (VALUES
   ('8326cdd20459', '1970-01-01 01:00:00+01', '{"openingTimes":[{"applicable_days":63,"periods":[{"startp":"06:00","endp":"22:00"}]},{"applicable_days":64,"periods":[{"startp":"07:00","endp":"21:00"}]}]}'),
   ('d392f7532218', '1970-01-01 01:00:00+01', '{"openingTimes":[{"applicable_days":31,"periods":[{"startp":"06:00","endp":"22:00"}]},{"applicable_days":64,"periods":[{"startp":"09:00","endp":"22:00"}]},{"applicable_days":32,"periods":[{"startp":"08:00","endp":"22:00"}]}]}')
) v (id, first_active, openingtimes_json)
CROSS APPLY OPENJSON (v.openingtimes_json, '$.openingTimes') j1
CROSS APPLY OPENJSON (j1.[value]) WITH (
   applicable_days int '$.applicable_days',
   startp varchar(5) '$.periods[0].startp',
   endp varchar(5) '$.periods[0].endp'
) j2

Result:

id           subsq  first_active           applicable_days   startp  endp
8326cdd20459     1  1970-01-01 01:00:00+01              63   06:00   22:00
8326cdd20459     2  1970-01-01 01:00:00+01              64   07:00   21:00
d392f7532218     1  1970-01-01 01:00:00+01              31   06:00   22:00
d392f7532218     2  1970-01-01 01:00:00+01              64   09:00   22:00
d392f7532218     3  1970-01-01 01:00:00+01              32   08:00   22:00