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":"US*mac*X*here*738*1*2*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!
Hi @ms4446
Would you have any guidance about the post above? Thank you in advance for your help?
User | Count |
---|---|
5 | |
1 | |
1 | |
1 | |
1 |