时间戳

首页 > 解决方案 > Instagram JSON 导出。使用 OPENJSON 透视子数组

json - Instagram JSON 导出。使用 OPENJSON 透视子数组

问题描述

我刚刚收到了来自 Instragram 的类似数据，文件的扩展名为 .json。

{"media_likes": [["2019-12-10T17:49:02", "feojo"], 
                 ["2019-12-10T14:11:53", "sollylun"],
                 ["2016-02-16T17:05:43", "ser_mart"]], 
"comment_likes":[["2019-11-28T18:05:04", "kerslyrikatel"], 
                 ["2019-11-28T18:05:03", "jakar"],
                 ["2019-12-10T14:11:53", "sollylun"]]}

我可以在值列中返回子数组，但不知道谁将这些数组转换为“日期”和“用户”列：

   DECLARE @JSON1 NVARCHAR(MAX)
    SELECT @JSON1 = '{
                     "media_likes": [
                                     ["2019-12-10T17:49:02", "feonaleejones"], 
                                     ["2019-12-10T14:11:53", "sollyluna021"],
                                     ["2016-02-16T17:05:43", "serene_martin"]
                                    ], 
                      "comment_likes":[
                                      ["2019-11-28T18:05:04", "kerstinslyrikatelier"], 
                                      ["2019-11-28T18:05:03", "jan.kart"],
                                      ["2019-12-10T14:11:53", "sollyluna021"]
                                      ]
                      }'

SELECT [key],value 
  FROM OPENJSON(@JSON1,'$.media_likes') 

key value
0   ["2019-12-10T17:49:02", "feonaleejones"]
1   ["2019-12-10T14:11:53", "sollyluna021"]
2   ["2016-02-16T17:05:43", "serene_martin"]

如何旋转两个集合数组的值？

约翰

标签： jsontsqlinstagram

解决方案

select 
   DECLARE @JSON1 NVARCHAR(MAX)
    SELECT @JSON1 = '{
                     "media_likes": [
                                     ["2019-12-10T17:49:02", "feonaleejones"], 
                                     ["2019-12-10T14:11:53", "sollyluna021"],
                                     ["2016-02-16T17:05:43", "serene_martin"]
                                    ], 
                      "comment_likes":[
                                      ["2019-11-28T18:05:04", "kerstinslyrikatelier"], 
                                      ["2019-11-28T18:05:03", "jan.kart"],
                                      ["2019-12-10T14:11:53", "sollyluna021"]
                                      ]
                      }'

SELECT likedate,userlike 
  FROM OPENJSON(@JSON1,'$.media_likes') 
  WITH (
       likedate datetime '$[0]',
       userlike varchar(128) '$[1]'
       )

推荐阅读