Json 对行 [英] Json pairs to rows
本文介绍了Json 对行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我又开始用 JSON 文档攻击 SQL Server 2016.我通过网络深入挖掘获得了一些运气，但是我仍然无法弄清楚如何将我的 json 结果转换为具有多个值列的行.
I am back on attacking SQL Server 2016 with JSON documents. I have had some luck through deep digging through the net, however I still cannot figure out how to turn my json results into rows with multiple value columns.
我想创造这样的东西:
+-----------+-------+------------+-----------+-------+------------------+---------+-----+-------+-------+---------+
| DateTime | Side | Identifier | Color | Level | SDSa | Current | LEF | Sys | Membr | SDS |
+-----------+-------+------------+-----------+-------+------------------+---------+-----+-------+-------+---------+
| 10/1/2018 | 15:01 | Left | 107604736 | Red | 0.65 5.053539157 | 0 | 0 | BOBJ7 | 5 | 1 |
| 10/2/2018 | 15:01 | Left | 107604737 | Red | 0.65 5.053539157 | 0.3035 | 1 | BOBJ8 | 6 | 0.98962 |
| 10/3/2018 | 15:01 | Left | 107604738 | Red | 0.65 5.053539157 | 0.6070 | 2 | BOBJ9 | 7 | 0.96576 |
+-----------+-------+------------+-----------+-------+------------------+---------+-----+-------+-------+---------+
我学会了如何使用
CROSS APPLY OPENJSON(JSON_QUERY(Data, '$.Measurements[1].SDS')) as X
在挖掘查询中的一点点，但是，我不知道如何链接相同长度的 json 文件中嵌套测量结果集中的其他数组.我将不胜感激您的帮助！谢谢！
A little bit in a query from digging, however, I do not know how to link other arrays in my result sets of the nested measurements in a json file that are of the same length. I would appreciate your help! Thanks!
PS:我正在尝试创建一个视图供其他人使用此查询.
PS: I am trying to create a view for others to query with this.
这里是文档的一个例子:
Here is an example of the document:
{
"DateTime": "10/1/2018 3:01:01 PM",
"Side": "Left",
"Identifier": "107604736",
"Color": "Red",
"Level": 0.65,
"SDSa": 5.0535391569137573,
"LEF": 0.0,
"System": "BOBJ7",
"Membrane": 5,
"Current": [
0.0,
0.303535521,
0.607071042,
0.910606563,
1.21414208,
1.51767755,
1.82121313,
2.12474871,
2.42828417,
2.73181963,
3.035355,
3.33889079,
3.64242625,
3.94596171,
4.24949741,
4.553033,
4.85656834,
5.160104,
5.46363926,
5.76717472,
6.07071,
6.374246,
6.67778158,
6.981317,
7.2848525,
7.588388,
7.89192343,
8.195459,
8.498995,
8.80253,
9.106066,
9.409601,
9.713137,
10.0166721,
10.3202076,
10.6237431,
10.9272785,
11.230814,
11.5343494,
11.8378849,
12.14142,
12.4449568,
12.7484922,
13.0520277,
13.3555632,
13.6590986,
13.9626341,
14.26617,
14.569705,
14.87324,
15.1767759,
15.4803114,
15.7838469,
16.0873833,
16.3909187,
16.6944542,
16.99799,
17.3015251,
17.60506,
17.908596,
18.2121315,
18.515667,
18.8192024,
19.1227379,
19.4262733,
19.7298088,
20.0333443,
20.33688,
20.6404152,
20.94395,
21.2474861,
21.5510216,
21.854557,
22.1580925,
22.461628,
22.7651634,
23.0686989,
23.3722343,
23.67577,
23.9793053,
24.28284,
24.5863781,
24.8899136,
25.193449,
25.4969845,
25.80052,
26.1040554,
26.40759,
26.7111263,
27.0146618,
27.3181973,
27.6217327,
27.9252682,
28.2288036,
28.53234,
28.8358746,
29.13941,
29.4429455,
29.74648,
30.0500164
],
"Measurements": [
{
"FPOS": 1,
"Orient": "H",
"SDS50": 6.8644590377807617,
"ImageN": 0,
"SDS": [
1.0,
0.9896224,
0.9657684,
0.937525332,
0.9123899,
0.888601959,
0.8634422,
0.8382806,
0.8161909,
0.7984247,
0.7806436,
0.7606637,
0.739305735,
0.720110357,
0.7029224,
0.6836812,
0.660055459,
0.6333771,
0.6086391,
0.5865341,
0.5649619,
0.540699542,
0.5150109,
0.4912444,
0.470531732,
0.4510031,
0.429589,
0.407328546,
0.386515945,
0.369066834,
0.353259325,
0.336980551,
0.3197548,
0.302164435,
0.2844041,
0.264812022,
0.2432023,
0.221351326,
0.203265771,
0.190894246,
0.183355331,
0.178263873,
0.17374523,
0.169159114,
0.1635048,
0.15626812,
0.148360759,
0.141854659,
0.1383282,
0.136285663,
0.133377567,
0.127650827,
0.119844966,
0.111289464,
0.103005029,
0.09480265,
0.0866060555,
0.07927561,
0.0740468055,
0.07167137,
0.07141984,
0.07215432,
0.07259899,
0.07151956,
0.0681445152,
0.06242689,
0.05562524,
0.0492420681,
0.04441181,
0.0410645567,
0.0388742052,
0.0380086526,
0.03860841,
0.04033492,
0.04170464,
0.04166763,
0.0399215743,
0.0370087326,
0.0335897,
0.0301406663,
0.02666917,
0.023069948,
0.0193837862,
0.0161479469,
0.0140230907,
0.0133305294,
0.0137383873,
0.0145777585,
0.0155265825,
0.0166243,
0.01805606,
0.01932042,
0.0196347721,
0.01826151,
0.0154753188,
0.012052347,
0.008844916,
0.00625393027,
0.004384732,
0.00334442779
],
"Processed": false
},
{
"FPOS": 2,
"Orient": "H",
"SDS50": 3.2426192760467529,
"ImageN": 0,
"SDS": [
1.0,
0.984369457,
0.940739632,
0.8834237,
0.8255927,
0.7736917,
0.7277459,
0.681687,
0.6329853,
0.582352459,
0.5328522,
0.48483035,
0.436798245,
0.388586164,
0.3424556,
0.302069068,
0.267472,
0.237092659,
0.209047124,
0.184123,
0.163114548,
0.144511625,
0.126265839,
0.107269846,
0.08942619,
0.07420107,
0.06193066,
0.0517099164,
0.0435060449,
0.0383287631,
0.0352067538,
0.03245497,
0.0286534447,
0.0242379941,
0.0201460086,
0.016266821,
0.0121985339,
0.008013846,
0.00560544431,
0.00502379332,
0.004533774,
0.004487538,
0.00551742641,
0.007546712,
0.009179788,
0.009129186,
0.007475236,
0.005305866,
0.004060863,
0.00462530646,
0.00631512143,
0.00748074846,
0.007632819,
0.006844715,
0.00562061928,
0.005166012,
0.006103046,
0.00768285431,
0.009006567,
0.009526576,
0.009514096,
0.009059127,
0.008065625,
0.006526997,
0.00434135273,
0.00191119267,
0.00254829624,
0.006861757,
0.0108742379,
0.0131905386,
0.013858974,
0.0132871456,
0.012235119,
0.01142512,
0.01094334,
0.0107232835,
0.0107062105,
0.010995524,
0.0112270387,
0.0102753676,
0.00612402242,
0.0010008571,
0.00318561238,
0.009038351,
0.0119382814,
0.0132263815,
0.013723935,
0.0133564435,
0.0129926438,
0.0127558094,
0.011888911,
0.009955382,
0.00670363754,
0.00246145763,
0.000897339836,
0.0037357898,
0.006457583,
0.007566943,
0.007532189,
0.006015837
],
"Processed": false
}}
推荐答案
通过获取单行数据并交叉连接 SDS 和 Current 字段，您将获得一个重复出现一次的数据的记录集以及Current 和 SDS 值按提供的顺序排列.
By taking the single row of data and cross joining the SDS and Current field, you get a recordset that repeats the data that is present one time and a combination of the Current and SDS values in the order they were provided.
DECLARE @test nvarchar(max)= N' {
"DateTime": "10/1/2018 3:01:01 PM",
"Side": "Left",
"Identifier": "107604736",
"Color": "Red",
"Level": 0.65,
"SDSa": 5.0535391569137573,
"LEF": 0.0,
"System": "BOBJ7",
"Membrane": 5,
"Current": [
0.0,
0.303535521,
0.607071042,
0.910606563,
1.21414208,
1.51767755,
1.82121313,
2.12474871,
2.42828417,
2.73181963,
3.035355,
3.33889079,
3.64242625,
3.94596171,
4.24949741,
4.553033,
4.85656834,
5.160104,
5.46363926,
5.76717472,
6.07071,
6.374246,
6.67778158,
6.981317,
7.2848525,
7.588388,
7.89192343,
8.195459,
8.498995,
8.80253,
9.106066,
9.409601,
9.713137,
10.0166721,
10.3202076,
10.6237431,
10.9272785,
11.230814,
11.5343494,
11.8378849,
12.14142,
12.4449568,
12.7484922,
13.0520277,
13.3555632,
13.6590986,
13.9626341,
14.26617,
14.569705,
14.87324,
15.1767759,
15.4803114,
15.7838469,
16.0873833,
16.3909187,
16.6944542,
16.99799,
17.3015251,
17.60506,
17.908596,
18.2121315,
18.515667,
18.8192024,
19.1227379,
19.4262733,
19.7298088,
20.0333443,
20.33688,
20.6404152,
20.94395,
21.2474861,
21.5510216,
21.854557,
22.1580925,
22.461628,
22.7651634,
23.0686989,
23.3722343,
23.67577,
23.9793053,
24.28284,
24.5863781,
24.8899136,
25.193449,
25.4969845,
25.80052,
26.1040554,
26.40759,
26.7111263,
27.0146618,
27.3181973,
27.6217327,
27.9252682,
28.2288036,
28.53234,
28.8358746,
29.13941,
29.4429455,
29.74648,
30.0500164
],
"Measurements": [
{
"FPOS": 1,
"Orient": "H",
"SDS50": 6.8644590377807617,
"ImageN": 0,
"SDS": [
1.0,
0.9896224,
0.9657684,
0.937525332,
0.9123899,
0.888601959,
0.8634422,
0.8382806,
0.8161909,
0.7984247,
0.7806436,
0.7606637,
0.739305735,
0.720110357,
0.7029224,
0.6836812,
0.660055459,
0.6333771,
0.6086391,
0.5865341,
0.5649619,
0.540699542,
0.5150109,
0.4912444,
0.470531732,
0.4510031,
0.429589,
0.407328546,
0.386515945,
0.369066834,
0.353259325,
0.336980551,
0.3197548,
0.302164435,
0.2844041,
0.264812022,
0.2432023,
0.221351326,
0.203265771,
0.190894246,
0.183355331,
0.178263873,
0.17374523,
0.169159114,
0.1635048,
0.15626812,
0.148360759,
0.141854659,
0.1383282,
0.136285663,
0.133377567,
0.127650827,
0.119844966,
0.111289464,
0.103005029,
0.09480265,
0.0866060555,
0.07927561,
0.0740468055,
0.07167137,
0.07141984,
0.07215432,
0.07259899,
0.07151956,
0.0681445152,
0.06242689,
0.05562524,
0.0492420681,
0.04441181,
0.0410645567,
0.0388742052,
0.0380086526,
0.03860841,
0.04033492,
0.04170464,
0.04166763,
0.0399215743,
0.0370087326,
0.0335897,
0.0301406663,
0.02666917,
0.023069948,
0.0193837862,
0.0161479469,
0.0140230907,
0.0133305294,
0.0137383873,
0.0145777585,
0.0155265825,
0.0166243,
0.01805606,
0.01932042,
0.0196347721,
0.01826151,
0.0154753188,
0.012052347,
0.008844916,
0.00625393027,
0.004384732,
0.00334442779
],
"Processed": false
},
{
"FPOS": 2,
"Orient": "H",
"SDS50": 3.2426192760467529,
"ImageN": 0,
"SDS": [
1.0,
0.984369457,
0.940739632,
0.8834237,
0.8255927,
0.7736917,
0.7277459,
0.681687,
0.6329853,
0.582352459,
0.5328522,
0.48483035,
0.436798245,
0.388586164,
0.3424556,
0.302069068,
0.267472,
0.237092659,
0.209047124,
0.184123,
0.163114548,
0.144511625,
0.126265839,
0.107269846,
0.08942619,
0.07420107,
0.06193066,
0.0517099164,
0.0435060449,
0.0383287631,
0.0352067538,
0.03245497,
0.0286534447,
0.0242379941,
0.0201460086,
0.016266821,
0.0121985339,
0.008013846,
0.00560544431,
0.00502379332,
0.004533774,
0.004487538,
0.00551742641,
0.007546712,
0.009179788,
0.009129186,
0.007475236,
0.005305866,
0.004060863,
0.00462530646,
0.00631512143,
0.00748074846,
0.007632819,
0.006844715,
0.00562061928,
0.005166012,
0.006103046,
0.00768285431,
0.009006567,
0.009526576,
0.009514096,
0.009059127,
0.008065625,
0.006526997,
0.00434135273,
0.00191119267,
0.00254829624,
0.006861757,
0.0108742379,
0.0131905386,
0.013858974,
0.0132871456,
0.012235119,
0.01142512,
0.01094334,
0.0107232835,
0.0107062105,
0.010995524,
0.0112270387,
0.0102753676,
0.00612402242,
0.0010008571,
0.00318561238,
0.009038351,
0.0119382814,
0.0132263815,
0.013723935,
0.0133564435,
0.0129926438,
0.0127558094,
0.011888911,
0.009955382,
0.00670363754,
0.00246145763,
0.000897339836,
0.0037357898,
0.006457583,
0.007566943,
0.007532189,
0.006015837
],
"Processed": false
}]}';
SELECT *
FROM (
SELECT *
FROM OPENJSON(@test)
WITH (
DateTime nvarchar(max) '$.DateTime'
, Side nvarchar(max) '$.Side'
, Identifier nvarchar(max) '$.Identifier'
, Color nvarchar(max) '$.Color'
, Level nvarchar(max) '$.Level'
, SDSa nvarchar(max) '$.SDSa'
, Lef nvarchar(max) '$.LEF'
, [Sys] nvarchar(max) '$.System'
, Membr nvarchar(max) '$.Membrane'
)
) baseData
CROSS JOIN (
SELECT CurrrentData.Value AS [CurrrentData],SdsData.Value AS [SdsData]
FROM OPENJSON(JSON_QUERY(@test, '$.Current')) CurrrentData
INNER JOIN OPENJSON(JSON_QUERY(@test, '$.Measurements[1].SDS')) SdsData ON SdsData.[key]=CurrrentData.[key]
) addlData
这篇关于Json 对行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
