none
sql server 2016+json RRS feed

  • 问题

  • 请问,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) )

    2017年3月28日 5:33

答案

  •  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 ;


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

    2017年3月28日 9:01
    版主

全部回复

  •  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 ;


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

    2017年3月28日 9:01
    版主
  • 谢谢!

    2017年3月28日 9:07
  • 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

    2017年3月29日 5:10