sql server 2016+json

Question

请问，tsql如何更改才能使userchecktime返回2行数据？

谢谢！

declare @json nvarchar(max)
set @json=N'
[
{
  "errmsg": "ok",
  "recordresult": [
    {
      "recordId": 798440329,
      "sourceType": "AUTO_CHECK",
      "workDate": 1490630400000,
      "locationResult": "Normal",
      "planId": 3987982940,
      "baseCheckTime": 1490661600000,
      "id": 2114624269,
      "groupId": 26162493,
      "userCheckTime": 1490661449000,
      "userId": "056133214226234577",
      "checkType": "OnDuty",
      "timeResult": "Normal",
    },
    {
      "recordId": 799402941,
      "sourceType": "USER",
      "workDate": 1490630400000,
      "locationResult": "Normal",
      "planId": 3987982941,
      "baseCheckTime": 1490692200000,
      "id": 2136374572,
      "groupId": 26162493,
      "userCheckTime": 1490664175000,
      "userId": "056133214226234577",
      "checkType": "OffDuty",
      "timeResult": "Early",
    }
  ],
  "errcode": 0
}
]'

select *
from openjson(@json)
with (ss varchar(100) '$.recordresult[0].userCheckTime',
ss1 varchar(100) '$.recordresult[0].userCheckTime',
ss2 varchar(100) '$.recordresult[1].userCheckTime')

select *
from openjson(@json,'$.recordresult')
with (userCheckTime varchar(100) )

Answer 1

 select dateadd(second, recordresult.userCheckTime/1000, '1970-1-1') as userCheckTime
 from openjson(@json)
 with (recordresult nvarchar(max) '$.recordresult' as json)
 cross apply openjson(recordresult)
 with (userCheckTime bigint '$.userCheckTime')
  as recordresult ;

---

想不想时已是想，不如不想都不想。

Answer 2

谢谢！

Answer 3

1.json数据格式有错。

2.这个就是一个嵌套，可以先把需要的json取出来然后在转换和查找。

declare @json nvarchar(max)
 set @json=N'
 [
 {
   "errmsg": "ok",
   "recordresult": [
     {
       "recordId": 798440329,
       "sourceType": "AUTO_CHECK",
       "workDate": 1490630400000,
       "locationResult": "Normal",
       "planId": 3987982940,
       "baseCheckTime": 1490661600000,
       "id": 2114624269,
       "groupId": 26162493,
       "userCheckTime": 1490661449000,
       "userId": "056133214226234577",
       "checkType": "OnDuty",
       "timeResult": "Normal"
     },
     {
       "recordId": 799402941,
       "sourceType": "USER",
       "workDate": 1490630400000,
       "locationResult": "Normal",
       "planId": 3987982941,
       "baseCheckTime": 1490692200000,
       "id": 2136374572,
       "groupId": 26162493,
       "userCheckTime": 1490664175000,
       "userId": "056133214226234577",
       "checkType": "OffDuty",
       "timeResult": "Early"
     }
   ],
   "errcode": 0
 }
 ]'
 
  select @json=recordresult
 from openjson(@json )
 with (recordresult nvarchar(max) '$.recordresult' as json) 

  select userCheckTime
 from openjson(@json )
 with (userCheckTime nvarchar(max) '$.userCheckTime')

---

family as water