Hi everyone,
I’m facing a major issue this week and need a solution as quickly as possible. I have a table with a JSON field named source_data, and I’m trying to transform its structure to extract all values. Here’s an example of the current structure in source_data:
{“_id”:{ “$oid”:“648f659e7fxxx”}, “connectorType”:{ “format”:“SOxxx”, “standard”:“CEl_xxx”}, “electricalCharacteristics”:{ “maxAmperageInA”:18, “maxElectricPowerInW”:34000, “maxVoltageInV”: {“$numberDecimal”:“230.000000000”}, “phases”:“L1_L5_L4”, “powerType”:“AC_3_PHASE”}, “entityInfo”:{ “auditInfo”:{“created”:{“timestamp”:{“$date”:“1970-01-01T00:00:00Z”},“user”:“”}}, “code”:“”, “externalIds”:[{“id”:“111-0”,“origin”:“test”,“type”:“testlegacy”},{“id”:“USmacXhere73812*1”,“origin”:“test”,“type”:“testLEGACY”},{“id”:“01:13:G6:61:FD:RE//3”,“origin”:“TEST”,“type”:“OD”}],“externalLinks”:,“organizations”:,“ownerRef”:{“code”:“”,“debugLabel”:“?”,“id”:“6665”,“organizationRef”:{“id”:“554”,“type”:“Orga”},“serviceType”:“MC”},“shortLabel”:“”,“statuses”:[{“status”:“TEST”,“timestamp”:{“$date”:“1999-06-23T10:21:00.524Z”}}],“tags”:{“SETUP_LOCK”:“test”}}, “evseRef”:{“id”:“648f659e7fxxx”,“type”:“EV”}, } |
|---|
To transform this into a more structured format, I used the following query:
Select
JSON_VALUE(source_data, '$._id.oid') as id,
STRUCT(
JSON_VALUE(source_data, "$.connectorType.format") AS Format,
JSON_VALUE(source_data,"$.connectiorType.standard") AS Standard
) AS ConnectorType,
JSON_VALUE(source_data,"$.orderNumber") AS orderNumber
from Jsontable
However, I want to make this process dynamic and scalable, as I have multiple tables with different field configurations. Ideally, I’d like to create a query that automatically navigates through each JSON object and constructs new tables with the desired structure for all field without prior definition .
If anyone has experience with this kind of transformation or has suggestions for achieving this dynamically across different tables, I’d greatly appreciate your insights. Thank you in advance for your help!