以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  求助:JSON格式数据解析  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=91481)

--  作者:李孝春
--  发布时间:2016/10/11 22:02:00
--  求助:JSON格式数据解析
返回的值我需要得到title 、 author 、 url  、update_time这四个值,烦请各位前辈多多指导,谢谢!

此主题相关图片如下:22.png
按此在新窗口浏览图片


永久图文消息素材列表的响应如下:

{
   "total_count": TOTAL_COUNT,
   "item_count": ITEM_COUNT,
   "item": [{
       "media_id": MEDIA_ID,
       "content": {
           "news_item": [{
               "title": TITLE,
               "thumb_media_id": THUMB_MEDIA_ID,
               "thumb_url": THUMB_URL,
               "show_cover_pic": SHOW_COVER_PIC(0 / 1),
               "author": AUTHOR,
               "digest": DIGEST,
               "content": CONTENT,
               "url": URL,
               "content_source_url": CONTETN_SOURCE_URL
           },
           //多图文消息会在此处有多篇文章
           ]
        },
        "update_time": UPDATE_TIME
    },
    //可能有多个图文消息item结构
  ]
}
下面是JSON我清理下后的内容

{"item":[{

"media_id":"gwmL5Qi-95lfkB7dnSYm66KXipsv1QpQgoy7exD5HFY",

"content":{

"news_item":[{

"title":"【震惊】新买的手机号发现绑定支付宝!竟能从对方账户转钱,速",

"author":"",

"digest":"",

"content":"

"content_source_url":"",

"thumb_media_id":"gwmL5Qi-95lfkB7dnSYm6w02eWFqhdIVKaR4q9V_CU",

"show_cover_pic":0,

"url":"http:\\/\\/mp.weixin.qq.com\\/s?__biz=MzAxMzAzNjg0Mw==&mid=503499675&idx=1&sn=ce1c640d5c575b834119199f23fd6f54&chksm=005ed7df37295ec96eb7cc15cc7725cc90214bca813e0ac6f11a67b2151b70d69517be19d6fc#rd",

"thumb_url":"http:\\/\\/mmbiz.qpic.cn\\/mmbiz_png\\/zMPVPsVaWBBjSticlTSg3fIyXgExbRrHsrzB5uP8rRVX7pqHk9XpBgWZUnNfVk57FWqZDxVzicql1C7Ud63GUE3w\\/0?wx_fmt=png"},

 

{"title":"国庆节大事纪:19城市调控楼市、金价大暴跌",

"author":"",

"digest":"",

"content":"

"content_source_url":"",

"thumb_media_id":"gwmL5Qi-95lfkB7dnSYm63LULSyh2-IKy408OrVs56s",

"show_cover_pic":0,

"url":"http:\\/\\/mp.weixin.qq.com\\/s?__biz=MzAxMzAzNjg0Mw==&mid=503499675&idx=2&sn=8c9b36dc892f017023a5eca7ba2cacfd&chksm=005ed7df37295ec99cddd95b02b2f75bb305dce1b8a4fcc770e6ce5c018832bf2c1566a9c8c0#rd",

"thumb_url":"http:\\/\\/mmbiz.qpic.cn\\/mmbiz_png\\/zMPVPsVaWBBjSticlTSg3fIyXgExbRrHsqBe6BoxsQBU8mZHgmy503fPq7HghUG1xGnicHFWiaTC0RhehPVDSyxCw\\/0?wx_fmt=png"},

], "create_time":1475895222,

"update_time":1475895242},

"update_time":1475895242}],

"total_count":56,

"item_count":2}


[此贴子已经被作者于2016/10/11 22:05:20编辑过]

--  作者:李孝春
--  发布时间:2016/10/11 22:03:00
--  
这里是我测试的代码:
Dim postdata As String ="{""type"":""news"",""offset"":""0"",""count"":""2""}"
\'Dim dr As DataRow =DataTables("ACCESS_TOKEN").Find("公众号名称 = \'黔源科技\'")
\'Dim url = String.Format("https://api.weixin.qq.com/cgi-bin/material/batchget_material?access_token=" & dr("ACCESSTOKEN") & "")
Dim url = String.Format("https://api.weixin.qq.com/cgi-bin/material/batchget_material?access_token=SfjQStF9Bn3sULgsYc7aSMitqCbkkmcSCdFbMP9HAcVaQhyPz0TqkfnI0hzOXbFUrT_HPx9su5hDG4dAD4Vil0fDi3991gjtoDbDxgPukg3xhMPYtS5f_0BrAtVDnRIiRBWdAHAKUP")
Using ms As New System.IO.MemoryStream()
Dim bytes = ConvertHelper.EncodingToBytes(postdata, System.Text.Encoding.UTF8)
ms.Write(bytes, 0, bytes.Length)
ms.Seek(0, System.IO.SeekOrigin.Begin)
Dim jsonString = Functions.Execute("HttpPost",url, ms) \'通过POST向接口传输菜单数据,并取得返回结果
Dim json As String = jsonString 
Dim ScriptControl As Object, data  As Object, JscriptCode As String
JscriptCode = "function toObject(json) {eval(""var o=""+json);return o;}"
ScriptControl = CreateObject("MSScriptControl.ScriptControl")
With ScriptControl
.Language = "Javascript"
.Timeout = -1
.AddCode(JscriptCode)
data = .Run("toObject", json)
End With
output.Show(json)
MessageBox.Show(data.total_count)
MessageBox.Show(data.item_count)
MessageBox.Show(data.item(0).media_id)
\'MessageBox.Show(data.media_id)
\'MessageBox.Show(data.content)
\'MessageBox.Show(data.news_item)
\'MessageBox.Show(data.title)
End Using
测试结果如下:
MessageBox.Show(data.total_count)      得到56
MessageBox.Show(data.item_count)       得到2
MessageBox.Show(data.item(0).media_id)  提示找不到成员
得到错误
MessageBox.Show(data.item(0).media_id)  提示找不到成员

--  作者:李孝春
--  发布时间:2016/10/11 22:46:00
--  回复:(李孝春)这里是我测试的代码:Dim postdata A...
经过分析,貌似应该还需要增加写遍历的方法,按照先遍历第一层 然后第二层  类似循环读取  
现在只是找到了思路 但是确无法写代码 
还烦请各位大大多多指导啊  谢谢啦

--  作者:有点蓝
--  发布时间:2016/10/11 23:10:00
--  
汗,第一次用这玩意,居然不能强类型引用

Dim json As String  =  jsonString 

Dim ScriptControl As Object, data  As Object, JscriptCode As String
JscriptCode = "function toObject(json) {eval(""var o=""+json);return o;}"
ScriptControl = CreateObject("MSScriptControl.ScriptControl")
With ScriptControl
    .Language = "Javascript"
    .Timeout = -1
    .AddCode(JscriptCode)
    data = .Run("toObject", json)
End With
For Each obj As object In data.item
    Output.Show(obj.media_id)
    For Each item As object In obj.content.news_item
        Output.Show(item.title)
    Next
Next


--  作者:李孝春
--  发布时间:2016/10/12 19:28:00
--  回复:(有点蓝)汗,第一次用这玩意,居然不能强类型...
有点蓝老师   经过你的指导 我目前已经实现了如下获取效果:(黑色部分加粗是标注说明的,json生成的时候并无)

media_idgwmL5Qi-95lfkB7dnSYm66KXipsv1QpQgoy7exD5HFY
【update_time】1476065493
【title】
【1】新买的手机号发现绑定支付宝!竟能从对方账户转钱,速看??
【2】国庆节大事纪:19城市调控楼市、金价大暴跌
【3】2016南方电网云南国际有限责任公司招聘公告
【4】以后这三种人,不要去打扰他
【5】我不优秀,但我善良不虚伪
【url】
【1】http://mp.weixin.qq.com/s?__biz=MzAxMzAzNjg0Mw==&mid=503499675&idx=1&sn=ce1c640d5c575b834119199f23fd6f54&chksm=005ed7df37295ec96eb7cc15cc7725cc90214bca813e0ac6f11a67b2151b70d69517be19d6fc#rd
【2】http://mp.weixin.qq.com/s?__biz=MzAxMzAzNjg0Mw==&mid=503499675&idx=2&sn=8c9b36dc892f017023a5eca7ba2cacfd&chksm=005ed7df37295ec99cddd95b02b2f75bb305dce1b8a4fcc770e6ce5c018832bf2c1566a9c8c0#rd
【3】http://mp.weixin.qq.com/s?__biz=MzAxMzAzNjg0Mw==&mid=503499675&idx=3&sn=b2cbb0da8becf16ee0bf450173d2bab2&chksm=005ed7df37295ec956ba13865cf683321d0b8de92877b5082ea3d85f8b934d948e8dc292b472#rd
【4】http://mp.weixin.qq.com/s?__biz=MzAxMzAzNjg0Mw==&mid=503499675&idx=4&sn=a00623b8848284412a2d283c670f12ba&chksm=005ed7df37295ec998aca34867c4637799b12e314dc8550941687bdf5a8d70ccfe23f2465a53#rd
【5】http://mp.weixin.qq.com/s?__biz=MzAxMzAzNjg0Mw==&mid=503499675&idx=5&sn=3fd4f724d1ff668f226bfe1c0ff51c10&chksm=005ed7df37295ec997fdf0724af44d752d1eeb73177cd8ee6247fc1092365b2659026dee6ec0#rd


【media_idgwmL5Qi-95lfkB7dnSYm68rJVtcRTKnjQAQGM24ebSk
【update_time】1475199000
【title】
【1】继微信后支付宝也将提现收费:每人2万基础免费额度,余额宝转出依然免费
【2】有人算过一笔账,得出一惊人结论:中国人根本就不用买房!
【3】一生都学不完的计谋,没心眼的看看吧!
【4】???国1庆?放·假?通í知?
?ur】?
?】?http://mp.weixin.qq.com/s?__biz=MzAxMzAzNjg0Mw==&mid=503499663&idx=1&sn=8c7398bb861d5dc0ca47bd840927d717&chksm=005ed7cb37295edd09c1bb69507f58f7045474812fc6abfa64cda982f7bf17482453fc0ed632#rd
?】?http://mp.weixin.qq.com/s?__biz=MzAxMzAzNjg0Mw==&mid=503499663&idx=2&sn=e580f284342da69bb520bc41ae1f0462&chksm=005ed7cb37295edd52ac50c2bfd22b96795e5a8454d7468e7bf997dad012c214e150b34a5060#rd
?】?http://mp.weixin.qq.com/s?__biz=MzAxMzAzNjg0Mw==&mid=503499663&idx=3&sn=1c582c77ceef43fcae2bf3f47b35b8ef&chksm=005ed7cb37295edd47a0b50127d03264beef9a2ca82b32b9a2debe5863ce6a69bf3978e6b56f#rd
?】?http://mp.weixin.qq.com/s?__biz=MzAxMzAzNjg0Mw==&mid=503499663&idx=4&sn=f5d7f81a17d0dcf4187f7dba501e3a2a&chksm=005ed7cb37295edd8409eccaa564d5cb473340eba1bfc0612ead91cb1395a7d26fe7cc6381f9#rd


…………………………下面还有很多  省略
[此贴子已经被作者于2016/10/12 19:35:08编辑过]

--  作者:李孝春
--  发布时间:2016/10/12 19:33:00
--  回复:(有点蓝)汗,第一次用这玩意,居然不能强类型...

下面是实现操作的代码:

Dim postdata As String ="{""type"":""news"",""offset"":""0"",""count"":""10""}"
Dim url = String.Format("https://api.weixin.qq.com/cgi-bin/material/batchget_material?access_token=QabsAuaz9o85YgwM147Gt1_ZnHDTY09wkD0a8pyuC9-okZeXfmUfLd3S6HuW4-CTqh-8rml1-MtLUgFASDMZtPzbmrmWGxyUuj6ff12qcRN7jFe6X8avrPs0y_t5AImeHIYfAFAWXN")
Using ms As New System.IO.MemoryStream()
Dim bytes = ConvertHelper.EncodingToBytes(postdata, System.Text.Encoding.UTF8)
ms.Write(bytes, 0, bytes.Length)
ms.Seek(0, System.IO.SeekOrigin.Begin)
Dim json As String = Functions.Execute("HttpPost",url, ms)
Dim ScriptControl As Object, data  As Object, JscriptCode As String
JscriptCode = "function toObject(json) {eval(""var o=""+json);return o;}"
ScriptControl = CreateObject("MSScriptControl.ScriptControl")
With ScriptControl
    .Language = "Javascript"
    .Timeout = -1
    .AddCode(JscriptCode)
    data = .Run("toObject", json)
End With
For Each obj As object In data.item
    Output.Show(obj.media_id)
    Output.Show(obj.update_time)  
    For Each item As object In obj.content.news_item
        Output.Show(item.title)
    Next
    For Each item As object In obj.content.news_item
        Output.Show(item.url)
    Next
    For Each item As object In obj.content.news_item
        Output.Show(item.author)
    Next
Next
End Using

假设现在已经创建了一张“数据存放表”media_id【update_time】【title】【url】四个字段
怎么将【media_id【update_time】【title】【url】对应的【1】【2】【3】【4】【5】分别一一对应起来呢?
media_id【update_time】【1】【1】
media_id【update_time】【2】【2】
media_id【update_time】【3】【3】
media_id【update_time】【4】【4】
media_id【update_time】【5】【5】







--  作者:有点蓝
--  发布时间:2016/10/12 20:13:00
--  
Dim dr As DataRow
For Each obj As object In data.item
    For Each item As object In obj.content.news_item
        dr = DataTables("表A").AddNew
        dr("media_id") = obj.media_id
        dr("update_time") = obj.update_time
        dr("title") = item.title
        dr("url") = item.url
    Next
Next

--  作者:李孝春
--  发布时间:2016/10/12 20:36:00
--  回复:(有点蓝)Dim dr As DataRowFor Each obj As o...
Dim postdata As String ="{""type"":""news"",""offset"":""0"",""count"":""10""}"
Dim url = String.Format("https://api.weixin.qq.com/cgi-bin/material/batchget_material?access_token=QabsAuaz9o85YgwM147Gt1_ZnHDTY09wkD0a8pyuC9-okZeXfmUfLd3S6HuW4-CTqh-8rml1-MtLUgFASDMZtPzbmrmWGxyUuj6ff12qcRN7jFe6X8avrPs0y_t5AImeHIYfAFAWXN")
Using ms As New System.IO.MemoryStream()
Dim bytes = ConvertHelper.EncodingToBytes(postdata, System.Text.Encoding.UTF8)
ms.Write(bytes, 0, bytes.Length)
ms.Seek(0, System.IO.SeekOrigin.Begin)
Dim json As String = Functions.Execute("HttpPost",url, ms)
Dim ScriptControl As Object, data  As Object, JscriptCode As String
JscriptCode = "function toObject(json) {eval(""var o=""+json);return o;}"
ScriptControl = CreateObject("MSScriptControl.ScriptControl")
With ScriptControl
    .Language = "Javascript"
    .Timeout = -1
    .AddCode(JscriptCode)
    data = .Run("toObject", json)
End With
Dim dr As DataRow
For Each obj As object In data.item
    For Each item As object In obj.content.news_item
        dr = DataTables("统计表").AddNew
        dr("media_id") = obj.media_id
        dr("update_time") = obj.update_time
        dr("title") = item.title
        dr("url") = item.url
    Next
Next
End Using


图片点击可在新窗口打开查看此主题相关图片如下:22.png
图片点击可在新窗口打开查看

居然直接运行提示如下错误 找不到类型jscripttypeinfo的公共成员ITEM?

[此贴子已经被作者于2016/10/12 20:44:38编辑过]

--  作者:有点蓝
--  发布时间:2016/10/12 20:50:00
--  
msgbox(json)看看是什么内容
--  作者:李孝春
--  发布时间:2016/10/12 21:38:00
--  回复:(有点蓝)msgbox(json)看看是什么内容
有点蓝 老师 
经排查  是我这边参数刚刚不小心给小孩子改到了  没有对应生成那密钥 所以导致失败。

我想再请教一下,上面的操作实现了JSON数据的解析,并且写入了对应的表A。

作为进一步来说,我想把这些数据写入表后,保存下来之后,下一次再次获取JSON的时候,解析数据时,对于表A中已经有了的数据,不要再写入了,没有的数据再对应新增,这样的话我就需要增加一个条件判断,判断所解析出来的数据没有在表A中出现,但是当前数据比较多 有上万条,如果每次都比对的话,效率更慢,有没有更好的办法来解决这个问题呢?