具有相同名称值的VBA Json解析器VBA json解析器 [英] VBA Json parser with same name value VBA json parser
问题描述
我有以下JSON数据
"data": [
{
"id": "816051",
"type": "jobs",
"links": {
"careersite-job-url": "https://workoab.teamtailor.com/jobs/gdsgds",
"careersite-job-apply-url": "https://workoab.teamtailor.com/jobs/816051-0220-gdsgds/applications/new?secret=d93b3a3a-c433-4564-a21c-d07096060365",
"careersite-job-apply-iframe-url": "https://workoab.teamtailor.com/jobs/816051-0220-gdsgds/applications/new?iframe=true&secret=d93b3a3a-c433-4564-a21c-d07096060365",
"self": "https://api.teamtailor.com/v1/jobs/816051"
},
"attributes": {
"apply-button-text": "",
"body": "<p>Används ej.</p>",
"end-date": null,
"human-status": "unlisted",
"internal": false,
"picture": null,
"pinned": false,
"start-date": null,
"status": "unlisted",
"tags": [],
"title": "blablablabal",
"pitch": "Används ej.",
"external-application-url": "",
"name-requirement": "optional",
"resume-requirement": "optional",
"cover-letter-requirement": "optional",
"phone-requirement": "optional",
"created-at": "2020-01-24T09:19:53.940+01:00",
"sharing-image-layout": "default",
"mailbox": "job-d93b3a3a-c433-4564-a21c-d07096060365@inbound.teamtailor.com"
},
"relationships": {
"stages": {
"links": {
"self": "https://api.teamtailor.com/v1/jobs/816051/relationships/stages",
"related": "https://api.teamtailor.com/v1/jobs/816051/stages"
},
"data": [
{
"type": "stages",
"id": "4494024"
},
{
"type": "stages",
"id": "4494023"
},
{
"type": "stages",
"id": "4494022"
},
{
"type": "stages",
"id": "4494021"
},
{
"type": "stages",
"id": "4494020"
},
{
"type": "stages",
"id": "4494019"
},
{
"type": "stages",
"id": "4494018"
},
{
"type": "stages",
"id": "4494017"
},
{
"type": "stages",
"id": "4494016"
},
{
"type": "stages",
"id": "4494015"
},
{
"type": "stages",
"id": "4494014"
},
{
"type": "stages",
"id": "4494013"
},
{
"type": "stages",
"id": "4494012"
},
{
"type": "stages",
"id": "4494011"
我正在尝试使用VBA jsonparser获取所有的阶段ID，但是我没有设法得到它-我可以获取所有其他数据....
I am trying to get all of the stage ids here with the VBA jsonparser, but I dont manage to get it - i can get all other data...ay.
我尝试使用Select Case item.name和Case数据"-但是后来我不知道如何调用该数据-也许我的想法是错误的.即我试图在下面的为每个项目"添加选择大小写" id，但这只是给我一个错误..
I have tried to use Select Case item.name and Case "data" - but then i dont know how to call that data - or maybe my idea is just wrong. Ie i tried below "for each item" to add "select case "id" but that just gave me an error..
我的代码当前是这样的-它不会返回不同级别的"id"
My code is currently like this - which does not return the different levels of "id"
Option Explicit
Sub getdata()
Dim ws As Worksheet: Set ws = Worksheets("Datalastcall")
Sheets("DataLastCall").Select
Dim i As Long
i = 2
Dim nexturl As String
nexturl = "https://api.teamtailor.com/v1/jobs?include=stages&filter%5Bstatus%5D=unlisted"
Dim http As Object
Dim Url As String
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
With http
.Open "Get", Url, False
.SetRequestHeader "Accept", "application/vnd.api+json"
.SetRequestHeader "X-Api-Version", "20161108"
.SetRequestHeader "Authorization", "My Token"
.Send
End With
JSONresp = http.ResponseText
Set jsonObject = JsonConverter.ParseJson(JSONresp)
Dim dataitem1 As Dictionary
Dim dataitem2 As Dictionary
Dim p As Long
p = 3
For Each dataitem1 In jsonObject("data")
ws.Cells(i, 1) = dataitem1("attributes")("title")
ws.Cells(i, 2) = dataitem1("id")
last = jsonObject("links")("last")
nexturl = jsonObject("links")("next")
For Each dataitem2 In dataitem1("relationships")("stages")("data")
ws.Cells(i, p) = dataitem2("id")
p=p+1
Next
i = i + 1
p=3
Next
End Sub
我得到无效的程序.我尝试了几种其他与(1)连接的方法，因为似乎第二个数据集是另一个数组，并选择大小写等，但是我无法使其工作..代码可以很好地为我获取所有其他数据.我还尝试在作为字典"和作为对象"之间进行切换
I get invalid procedure. I have tried several other ways with connecting with (1) as it seems the second data set is another array, and select case etc, but i cant get it to work.. the code works fine getting all other data for me. I also tried to change between "as dictionary" and "as object"
我非常感谢您提供有关如何分阶段获得不同"id"的帮助.我猜是问题出在同一个名称的变体的多个级别上.即既是数据"又是"id"?
I would really appreciate any help on how to get the different "id"s under stages. I guess the problem is multiple levels of same names of variants. ie both "data" and "id" ?
output now looks like this... ie not getting each different stage id - and not matchning to my other "gets"
Jobname JobID Stageid1 Stageid2 Stageid3
Blablajobb 1234 567 567 567
BLANK?! Blank 568 568 568
推荐答案
我不太确定您为此使用的是哪个库，因此无法对其进行测试.但是我的猜测是，您首先需要一个词典Item或2.您必须遍历第一个数据数组，然后遍历 relationships/stages 对象中的基础数据数组.这样的事情应该起作用:
I'm not quite sure which library you use for this so I couldn't test it. But my guess is that you first need a dictionary Item or 2. You have to iterate over the first data array and then over the underlying data array in the relationships/stages object.
Something like this should work:
Dim dataItem1 As Dictionary, dataItem2 As Dictionary
For Each dataItem1 In jsonObject("data")
For Each dataItem2 In dataItem1("relationships")("stages")("data")
ws.Cells(i, p) = dataItem2("id")
ws.Cells(i, p + 1) = dataItem2("id")
ws.Cells(i, p + 3) = dataItem2("id")
i = i + 1
Next
Next
或者如果您只想要数据中的第一项(或者每次调用只包含一项)，您也可以这样做:
or if you only want the first item from data (or there only is one item per call) you could also do:
Dim dataItem1 As Dictionary
For Each dataItem1 In jsonObject("data")(1)
ws.Cells(i, p) = dataItem1("id")
ws.Cells(i, p + 1) = dataItem1("id")
ws.Cells(i, p + 3) = dataItem1("id")
i = i + 1
Next
这篇关于具有相同名称值的VBA Json解析器VBA json解析器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
