Forum Discussion

PaulJohnson's avatar
PaulJohnson
Qrew Cadet
5 years ago

Pipeline JSON Schema Samples

Good Evening Everyone

I am trying to import data into QB from an external location (https://www.travel-advisory.info/api) there is nothing confidential at all about this, it is the UK.gov travel advice api and it is free for anyone to use and all it contains is the current advice for travel either to or through 238 countries. There is no authentication of any kind required.

If I point the Fetch JSON pipeline to this site it returns a JSON Data source called Headers, if I look at the data by going to the pipeline step I can see all of the data. After reading I guessed that I needed to use an additional step called Iterate over JSON Steps, I put this in and I am not stumped over what to add into JSON Schema Sample. i have spent all day trying to both write my own or use the data to have sites like https://app.quicktype.io/ create one, in every case QB pipelines says they fail validation and I am not getting a soft squasy noise when I bang my head against the wall.

if anyone has a JSON sample schema that works please can you post it here so that I can see the kind of thing I am striving for. I have added the last one I worked on for reference.

Kindest regards
Paul
{
"$schema": "http://json-schema.org/draft-06/schema#",
"$ref": "#/definitions/Welcome",
"definitions": {
"Welcome": {
"type": "object",
"additionalProperties": false,
"properties": {
"api_status": {
"$ref": "#/definitions/APIStatus"
},
"data": {
"$ref": "#/definitions/Data"
}
},
"required": [],
"title": "Welcome"
},
"APIStatus": {
"type": "object",
"additionalProperties": false,
"properties": {
"request": {
"$ref": "#/definitions/Request"
},
"reply": {
"$ref": "#/definitions/Reply"
}
},
"required": [],
"title": "APIStatus"
},
"Reply": {
"type": "object",
"additionalProperties": false,
"properties": {
"cache": {
"type": "string"
},
"code": {
"type": "integer"
},
"status": {
"type": "string"
},
"note": {
"type": "string"
},
"count": {
"type": "integer"
}
},
"required": [],
"title": "Reply"
},
"Request": {
"type": "object",
"additionalProperties": false,
"properties": {
"item": {
"type": "string"
}
},
"required": [],
"title": "Request"
},
"Data": {
"type": "object",
"additionalProperties": false,
"properties": {
"DE": {
"$ref": "#/definitions/De"
}
},
"required": [],
"title": "Data"
},
"De": {
"type": "object",
"additionalProperties": false,
"properties": {
"iso_alpha2": {
"type": "string"
},
"name": {
"type": "string"
},
"continent": {
"type": "string"
},
"advisory": {
"$ref": "#/definitions/Advisory"
}
},
"required": [],
"title": "De"
},
"Advisory": {
"type": "object",
"additionalProperties": false,
"properties": {
"score": {
"type": "number"
},
"sources_active": {
"type": "integer"
},
"message": {
"type": "string"
},
"updated": {
"type": "string",
"format": "date-time"
},
"source": {
"type": "string",
"format": "uri",
"qt-uri-protocols": [
"https"
]
}
},
"required": [],
"title": "Advisory"
}
}
}

------------------------------
Paul J
------------------------------
Pipelines
  • BrianCafferelli's avatar
    BrianCafferelli
    Quickbase Staff
    5 years ago
    Hi Paul,

    The JSON Schema Sample should be an example of a single row or object from the JSON data you want to iterate over. To find a good sample, you'll need to call the API you're working with outside of Pipelines first - like with Postman. Or, when using Quick Base's new RESTful API, you can make calls directly from our API Portal (https://developer.quickbase.com/).

    Specifically how much of your JSON you will need to copy and paste as the schema sample depends on the structure of the JSON data you're working with. But keep in mind that Pipelines allows you to navigate to a specific part of a piece of JSON data so you only need a sample from the portion of your JSON data you will be iterating over.

    For example, I grabbed a copy of the Complete Project Manager app from the exchange. I want to use the QB RESTful API to run the Active Projects table report, then iterate over the results so I can work with each individual row in Pipelines. To do that, I try out the call in the API Portal linked above. I see that the JSON response has three portions: data, fields, and metadata. I only want the data, which contains the records for that report. I indicate this by entering "/data" as the JSON Records Path.

    When I make the call in the API Portal, I then start highlighting the text under the data section, until I see that I've reached the end of the first record. I can see that when the field IDs start to repeat. For this example, my JSON Schema sample would be:

    {
    "16": {
    "value": "Customer Service System Update"
    },
    "23": {
    "value": "2017-05-06"
    },
    "24": {
    "value": "2017-07-11"
    },
    "27": {
    "value": "High"
    },
    "28": {
    "value": "In-Progress"
    },
    "34": {
    "value": "https://team.quickbase.com/db/bqkcuvjtg?act=API_GenAddRecordForm&_fid_48=6&_fid_8=05-06-2017&z=h"
    },
    "81": {
    "value": "<img src=\"https://images.quickbase.com/si/16/227-rect_red.png\" title=\"Project Overdue\">"
    },
    "82": {
    "value": 12
    },
    "97": {
    "value": {
    "email": "albercruz@example.com",
    "id": "1002.czm8",
    "name": "a456123"
    }
    },
    "103": {
    "value": "Acme Staple Company"
    },
    "130": {
    "value": "javascript:void(copyMasterDetailButtonHandler('&copyFid=16&destrid=0&sourcerid=6', 'bqkcuvjta'))"
    }
    }

    ------------------------------
    Brian Cafferelli
    ------------------------------