error serializing dict to json and writing to bigquery

Question

This is the dictionary I have

my_dict1 = {'jobId': dict_values(['banned_phrase_model_', 'spica_4', 'spica_create_event1',  'spica_create_event2'])}

And I am trying insert this dictionary into bigquery table after serializing to json using the code below.

from google.cloud import bigquery
import json
from pydantic.json import pydantic_encoder
client1 = Clients.get_client(ClientType.BIGQUERY, name='x')
toJSON = json.dumps(my_dict1, default=pydantic_encoder).encode("utf-8")
data = json.loads(toJSON)
err1 = client1.insert_rows(table,[data])
print(err1)

The table in bigquery is one column jobId (STRING). The error I am getting is "TypeError: Object of type 'dict_values' is not JSON serializable". I tried using the function in the stackoverflow link as well. How to serialize Python dict to JSON Still it did not work. Please let me know your thoughts. Thanks.

score 0 · Accepted Answer · answered Apr 04 '22 at 21:06

The error is because python doesn't know how to represent SomeObject.

In this case you can validate the format of my_dict1. You can see this example

{"class":{"students":[{"id":5},{"id":12}]}}

Another option is to verify the type of the table schema in this case is STRING , for this field should be RECORD type or REPEATED.

You can see this example:

 - id
 - first_name
 - last_name
 - dob (date of birth)
 - addresses (a nested and repeated field)
   - addresses.status (current or previous)
   - addresses.address
   - addresses.city
   - addresses.state
   - addresses.zip
   - addresses.numberOfYears (years at the address)

The JSON looks like this

{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]}

{"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","addresses":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"},{"status":"previous","address":"321 Main Street","city":"Hoboken","state":"NJ","zip":"44444","numberOfYears":"3"}]}

error serializing dict to json and writing to bigquery

1 Answers1