Why is json_extract_scalar returning null

Question

I have a fairly simple string-formatted json column in a BigQuery database I am trying to flatten.

id	relationships
1	{'ownerObject': {'data': None}, 'investmentObject': {'data': {'type': 'assets', 'id': '40'}}, 'securityObject': {'data': None}, 'segmentObject': {'data': None}, 'dataItemObject': {'data': {'type': 'dataItems', 'id': '13161'}}, 'scenarioObject': {'data': {'type': 'scenarios', 'id': '13'}}}

Running:

SELECT 
  id,
  JSON_EXTRACT_SCALAR(relationships, '$.investmentObject.data.type') as Invest_Type
FROM periodicData

Gives me:

id	Invest_Type
1	null

I would expect the column Invest_Type to be = asset. But no matter what I try on this json column, I always end up with null:

• JSON_EXTRACT(relationships, '$.investmentObject.data.type') as Invest_Type = null
• JSON_EXTRACT(relationships, '$.dataItemObject.data.type') as Invest_Type = null
• JSON_EXTRACT(relationships, '$.dataItemObject') as Invest_Type = null
• JSON_EXTRACT(relationships, '$') as Invest_Type = null
• relationships as r = The full json (as expected)

I have been doing similar operations over the past week on a lot of tables with no issue, but somehow this table is stubborn. What could I be doing wrong?



How relationships looks in the schema


How relationships looks in the preview


Any help is appreciated!