JSON API Output is Incorrectly Formatted

Develop, Build & Integrate APIs & Integrations
,
1

I am pulling a list of my contacts via the API with Python. The JSON file that is being put by Hubspot is incorrectly formatted. When I run it through Pandas in Python to convert it to a CSV, I keep getting a ValueError. Below is a sample of what I get from the API.

{
 "results":[
 {
 "id":"101",
 "properties":{
 "createdate":"2020-06-05T15:18:37.746Z",
 "email":"someone@aplace.com",
 "firstname":"First",
 "hs_object_id":"101",
 "lastmodifieddate":"2020-08-12T15:17:35.104Z",
 "lastname":"Last"
 },
 "createdAt":"2020-06-05T15:18:37.746Z",
 "updatedAt":"2020-08-12T15:17:35.104Z",
 "archived":false
 }
 ],
 "paging":{
 "next":{
 "after":"452",
 "link":"https://api.hubapi.com/sampleurl.com"
 },
 "prev":null
 }
}

As it turns out, if I incase the last section of the JSON file in {} and move the ] to the second line from the end, the file reads properly. See the code below for a sample that works properly after two, slight modifications.

{
 "results": [
 {
 "id": "101",
 "properties": {
 "createdate": "2020-06-05T15:18:37.746Z",
 "email": "someone@aplace.com",
 "firstname": "First",
 "hs_object_id": "101",
 "lastmodifieddate": "2020-08-12T15:17:35.104Z",
 "lastname": "Last"
 },
 "createdAt": "2020-06-05T15:18:37.746Z",
 "updatedAt": "2020-08-12T15:17:35.104Z",
 "archived": false
 },
 {
 "paging": {
 "next": {
 "after": "452",
 "link": "https://api.hubapi.com/sampleurl.com"
 },
 "prev": null
 }
 }
 ]
}

Is there a way to correct the JSON code that is coming from the API? Or perhaps I am not pulling the code properly (I am using a simple GET from Python). See the code below.

import sys
import requests

url = "https://api.hubapi.com/crm/v3/objects/contacts"

querystring = {"limit":"10","paginateAssociations":"false","archived":"false","hapikey":"API Key"}

headers = {'accept': 'application/json'}

response = requests.request("GET", url, headers=headers, params=querystring)

print(response.text)

original_stdout = sys.stdout

with open('contacts.json', 'w') as f:
 sys.stdout = f
 print(response.text)
 sys.stdout = original_stdout

I feel like I’m missing something obvious here.

2

heyo @taran42 ,

Firstly, I must admit I an not experienced with python. (It is on my to do list. I promise)

The json that is being returned is valid, but obviously formatted to fit your needs…

Going to throw a couple smart people at this from the community to see if they have ideas

@Bryantworks , @jpsanchez , @Anton -- You all have thoughts on this?

Worst case scenario, if there is not a solve, we can put this in the Ideas forum.

Thanks!

3

Hi ,

My apologies for the delay,

Try this one. Don`t worry about the hapikey it´s from a fake develp portal and also the data.

import sys
import requests
import pandas as pd

url = "https://api.hubapi.com/crm/v3/objects/contacts"

querystring = {"limit":"10","paginateAssociations":"false","archived":"false","hapikey":"a08dfc1a-9786-4ced-9d9e-f5b096ba99fd"}

headers = {'accept': 'application/json'}

response = requests.request("GET", url, headers=headers, params=querystring)

#print(response.text)

original_stdout = sys.stdout

##with open('contacts.json', 'w') as f:
 ##sys.stdout = f
 ##print(response.text)
 ##sys.stdout = original_stdout

import json
def jprint(obj):
 # create a formatted string of the Python JSON object
 text = json.dumps(obj, sort_keys=True, indent=4)
 print(text)

jprint(response.json())

Here the response

{
 "results": [
 {
 "archived": false,
 "createdAt": "2019-10-21T20:25:14.863Z",
 "id": "1",
 "properties": {
 "createdate": "2019-10-21T20:25:14.863Z",
 "email": "coolrobot@hubspot.com",
 "firstname": "Cool",
 "hs_object_id": "1",
 "lastmodifieddate": "2020-05-25T15:14:06.167Z",
 "lastname": "Robot (Sample Contact)"
 },
 "updatedAt": "2020-05-25T15:14:06.167Z"
 },
 {
 "archived": false,
 "createdAt": "2019-10-21T20:25:15.113Z",
 "id": "51",
 "properties": {
 "createdate": "2019-10-21T20:25:15.113Z",
 "email": "bh@hubspot.com",
 "firstname": "Brian",
 "hs_object_id": "51",
 "lastmodifieddate": "2020-05-24T06:30:27.683Z",
 "lastname": "Halligan (Sample Contact)"
 },
 "updatedAt": "2020-05-24T06:30:27.683Z"
 },
 {
 "archived": false,
 "createdAt": "2019-11-13T12:39:50.532Z",
 "id": "101",
 "properties": {
 "createdate": "2019-11-13T12:39:50.532Z",
 "email": "123123|@312.com",
 "firstname": "adfadf",
 "hs_object_id": "101",
 "lastmodifieddate": "2020-05-16T15:47:28.713Z",
 "lastname": "adfasd"
 },
 "updatedAt": "2020-05-16T15:47:28.713Z"
 },
 {
 "archived": false,
 "createdAt": "2019-12-19T11:10:00.941Z",
 "id": "201",
 "properties": {
 "createdate": "2019-12-19T11:10:00.941Z",
 "email": "demo1@asmalljob.com",
 "firstname": "nombre1",
 "hs_object_id": "201",
 "lastmodifieddate": "2020-05-16T21:45:51.396Z",
 "lastname": "apellido2"
 },
 "updatedAt": "2020-05-16T21:45:51.396Z"
 }
 ]
}