sql - 如何从包含 json 查询的表中构建多活动卫星表?
问题描述
我有一张如下表:
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"}]}]}
我想根据 Data Vault 原则有一个卫星表,如下所示:
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 |
到目前为止,我只知道如何选择 json 查询的内容。例如,如果我运行
JSON_VALUE([openingtimes_json], '$.openingTimes[0}.applicable_days')
我的第一条记录是 63 分。
解决方案
您需要 SQL Server 2016+ 使用两个OPENJSON()
调用(使用默认和显式架构)和适当的APPLY
运算符来解析存储的 JSON。
作为注释,我假设$.periods
JSON 数组有一个项目(如果没有,则需要额外的APPLY
运算符和OPENJSON()
调用):
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
结果:
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
推荐阅读
- javascript - Angular 5 不使用 reCAPTCHA v2 + Reactive Forms 进行路由
- javascript - PriorityNavigation.js 因附加块而中断
- android - onRecieve 在 imageView 上显示单个波纹效果
- javascript - 在带有 Webpack 的 Electron 渲染器中使用 Node.js 插件
- c - gettimeofday() 微秒不限于秒以下
- c++ - C++ 排序坐标存储为向量中的对象
- android - 大安卓交换文件
- c++ - 我如何可视化这段代码创建了多少进程?
- php - 如何在codeigniter中将语言值从cookie附加到URL
- angular - Angular中的`ng lint --format=stylish`和`ng lint`有什么区别