Scenario: I have a dataframe which contains one row of data. Each column is an year and it has the relevant value. I am trying to use the data from this df to create a json style structure to pass to an API requests.post.

Closed 13 days ago.

I am working with a JSON file that is designed as such:

I’ve run into a strange issue with an API I am calling. I’m getting my data as a JSON with a string containing a JSON inside of it. Instead of being nested, for some reason it comes across as a string field inside the JSON. I’ve gotten to a point where I can change this original JSON into a Pandas Dataframe that can be seen below, but I need to eventually drop this into a table in a SQL database, but the JSON I am working with is significantly longer than the sample I am working with here, and as a result, the JSON is being cut off due to the maximum size of a varchar I can work with in SQL.

I’ve run into a strange issue with an API I am calling. I’m getting my data as a JSON with a string containing a JSON inside of it. Instead of being nested, for some reason it comes across as a string field inside of the JSON. I’ve gotten to a point where I can change this original JSON into a Pandas Dataframe that can be seen below, but I need to eventually drop this into a table in a SQL database, but the JSON I am working with is significantly longer than the sample I am working with here, and as a result the JSON is being cut off due to the maximum size of a varchar I can work with in SQL.

[
{
“ver”: “1”,
“dt”: 1699439129329,
“od”: “OBJECT_ID”,
“bin”: “Vbin”,
“myname”: “VME”,
“msgss”: [
{
“code”: “TLHGH”,
“Details”: {
“no”: 1,
“rep”: 4
},
“pngds”: [
{
“id”: “ID”,
“mpo”: 16,
“mkg”: “up”,
}
]
}
]
},
{
“ver”: “2”,
“dt”: 1699439129329,
“od”: “OBJECT_ID”,
“bin”: “Vbin”,
“myname”: “VME”,
“msgss”: [
{
“code”: “TLHGH”,
“Details”: {
“no”: 1,
“rep”: 4
},
“pngds”: [
{
“id”: “ID”,
“mpo”: 16,
“mkg”: “up”,
}
]
}
]
}
]

{
“ver”: “2”,
“dt”: 169,
“od”: “OBJECT_ID”,
“bin”: “Vbin”,
“myname”: “VME”,
“msgss”: [
{
“code”: “TLHGH1”,
“Details”: {
“no”: 1,
“rep”: 4
},
“pngds”: [
{
“id”: “ID1”,
“mpo”: 16,
“mkg”: “up”
}
]
},
{
“code”: “TLHGH2”,
“Details”: {
“no”: 2,
“rep”: 5
},
“pngds”: [
{
“id”: “ID2”,
“mpo”: 16,
“mkg”: “down”
}
]
}
]
}