APIs & Integrations

taran42
Contributor

JSON API Output is Incorrectly Formatted

SOLVE

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.

0 Upvotes
1 Accepted solution
jpsanchez
Solution
Contributor | Elite Partner
Contributor | Elite Partner

JSON API Output is Incorrectly Formatted

SOLVE

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"
        }
    ]
}

View solution in original post

2 Replies 2
dennisedson
HubSpot Product Team
HubSpot Product Team

JSON API Output is Incorrectly Formatted

SOLVE

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!

0 Upvotes
jpsanchez
Solution
Contributor | Elite Partner
Contributor | Elite Partner

JSON API Output is Incorrectly Formatted

SOLVE

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"
        }
    ]
}