How to open a JSON inside a column, which has a variable structure on SQL Server or similar

I have this table on SQL Server from which I want to extract a column information, but inside that column I have 730 rows of different structured JSON. In the past, I used OPENJSON but my data had a structured JSON, it worked like this:

SELECT	t._id as IDENT, oj.id, oj.CreatedAt, oj.from_nome, oj.from_id, oj.observacao
FROM	my.table as t
		CROSS APPLY OPENJSON(t.observacoes) 
		WITH
		(
			id NVARCHAR(36) '$.id',
			CreatedAt DATETIME2 '$.created_at',
			from_nome NVARCHAR(50) '$.from.nome',
			from_id NVARCHAR(50) '$.from.id',
			observacao NVARCHAR(MAX) '$.observacao.texto'
		) AS oj

But now, I don't have a fixed structure inside each row of said column, each cell has a different JSON structure, I will show one example (which is one row of said column):

[
   {
      "created_at":"09/03/2021 16:50:52"
   },
   {
      "tramite-yoeivxtq-parecer":{
         "tramite-yoeivxtq-parecer-texto":"text",
         "tramite-yoeivxtq-parecer-anexos":[
            {
               "name":"name",
               "width":0,
               "height":0,
               "status":[
                  {
                     "status":true
                  }
               ],
               "uploadedNow":true,
               "size":3033168
            }
         ]
      },
      "created_at":"26/04/2021 10:26:23",
      "created_at_iso":{
         "$date":"2021-04-26T13:26:23.168Z"
      },
      "from":{
         "userId":"auth0|5f9856efc6b72c007100caec",
         "name":"name"
      }
   },
   {
      "tramite-a4ovjmk0-parecer":{
         "tramite-a4ovjmk0-parecer-texto":"text",
         "tramite-a4ovjmk0-parecer-anexos":null
      },
      "created_at":"10/05/2021 16:10:26",
      "created_at_iso":{
         "$date":"2021-05-10T19:10:26.042Z"
      },
      "from":{
         "userId":"auth0|5f9856efc6b72c007100caec",
         "name":"xxx"
      }
   },
   {
      "tramite-e11cyklf-parecer":{
         "tramite-e11cyklf-parecer-texto":"text",
         "tramite-e11cyklf-parecer-anexos":null
      },
      "created_at":"12/05/2021 07:09:00",
      "created_at_iso":{
         "$date":"2021-05-12T10:09:00.403Z"
      },
      "from":{
         "userId":"auth0|5e5808ba05a0dc0c17160d4b",
         "name":"name"
      }
   }
]

I want to extract same as I did on later OPENJSON usage: row_id, created_at, from_name, from_id, text

But I can't get inside the JSON's because they do not have a solid structure like ".$.tramite-yoeivxtq-parecer.tramite-yoeivxtq-parecer-texto" and then the next one has a different key.

Also using SQL Server 2022 (RTM-GDR) (KB5021522) - 16.0.1050.5 (X64)

PS.: if it is not viable on SQL, a way to do it on Python would be appreciated

If the structure of your JSON is very dynamic, you could use a recursive common table expression to convert your JSON in a tabular format like this:

WITH cte_recursive
AS
(
	SELECT t.id, j1.[key] as Path,j1.[key],j1.[value],j1.[type]
	FROM #temp t
	OUTER APPLY OPENJSON(t.JS) j1
	UNION ALL
	SELECT c.id, CONCAT(c.Path,'/',j2.[key]) as Path,j2.[key],j2.[value],j2.[type]
	FROM cte_recursive c
	CROSS APPLY OPENJSON(c.[value]) j2
	WHERE c.type IN (4,5)
)
SELECT *
FROM cte_recursive c2
WHERE c2.[type] NOT IN (0,4,5);

If you're using SQL Server 2017 you could get the dynamic values by doing some nested OPENJSON magic:

select x.[key] AS id
, MAX(CASE WHEN v.[key] = 'created_at' THEN v.value END) /*OVER(PARTITION BY x.[key])*/ AS created_at
, MAX(CASE WHEN v.[key] = 'from' then JSON_VALUE(v.value, '$.name') END) /*OVER(PARTITION BY x.[key]) */ AS from_nome
, MAX(CASE WHEN v.[key] = 'from' then JSON_VALUE(v.value, '$.userId') END) /*OVER(PARTITION BY x.[key]) */ AS from_id
, MAX(CASE WHEN v.[key] like '%tramite%' then JSON_VALUE(v.value, '$."' + v.[key] + '-texto"') END) /*OVER(PARTITION BY x.[key])*/ AS someText
--, *
from openjson(N'
[
   {
      "created_at":"09/03/2021 16:50:52"
   },
   {
      "tramite-yoeivxtq-parecer":{
         "tramite-yoeivxtq-parecer-texto":"text",
         "tramite-yoeivxtq-parecer-anexos":[
            {
               "name":"name",
               "width":0,
               "height":0,
               "status":[
                  {
                     "status":true
                  }
               ],
               "uploadedNow":true,
               "size":3033168
            }
         ]
      },
      "created_at":"26/04/2021 10:26:23",
      "created_at_iso":{
         "$date":"2021-04-26T13:26:23.168Z"
      },
      "from":{
         "userId":"auth0|5f9856efc6b72c007100caec",
         "name":"name"
      }
   },
   {
      "tramite-a4ovjmk0-parecer":{
         "tramite-a4ovjmk0-parecer-texto":"text",
         "tramite-a4ovjmk0-parecer-anexos":null
      },
      "created_at":"10/05/2021 16:10:26",
      "created_at_iso":{
         "$date":"2021-05-10T19:10:26.042Z"
      },
      "from":{
         "userId":"auth0|5f9856efc6b72c007100caec",
         "name":"xxx"
      }
   },
   {
      "tramite-e11cyklf-parecer":{
         "tramite-e11cyklf-parecer-texto":"text",
         "tramite-e11cyklf-parecer-anexos":null
      },
      "created_at":"12/05/2021 07:09:00",
      "created_at_iso":{
         "$date":"2021-05-12T10:09:00.403Z"
      },
      "from":{
         "userId":"auth0|5e5808ba05a0dc0c17160d4b",
         "name":"name"
      }
   }
]') x
CROSS APPLY OPENJSON(x.value) v
where CAST(x.[key] AS INT) > 0
group by x.[key]

Basically, you first openjson to get the contents and then shred down every key in the nested to own rows, and then match the keys you're looking for, while keeping the original row_id as a way to group the values together for the final result.

To get the dynamic key name, you build the key from the key id (this is not possible in SQL Server 2016 and earlier)

For debug purposes, you can uncomment the partition by and comment out group by and just select * to see how this stuff works.