Como faço para converter um varchar de json aninhado em jsonb e depois nivelá-lo?

Quanto ao equivalente do PG, é este SQL:

with recursive json_tree as
(
    select 
        '[{"value": {
        "id":"1",
        "desc":"widget",
        "colours":[{
             "hue":"red",
             "tint":"light"
             },{
             "hue":"green",
             "tint":"light"
             },{
             "hue":"blue",
             "tint":"dark"
             }],
        "isActive":true
        }}]'::json as json_value, 
        array[]::text[] as json_path
    union all
    select 
        coalesce(t_obj.value, t_array.value) as json_value, 
        t.json_path || coalesce(t_obj.key, (t_array.key - 1)::text) as json_path
    from json_tree t
    left join json_each(case when json_typeof(t.json_value) = 'object' then t.json_value end) t_obj on true
    left join json_array_elements(case when json_typeof(t.json_value) = 'array' then t.json_value end) with ordinality t_array(value, key) on true
    where json_typeof(t.json_value) in ('array', 'object')  
)
select array_to_string(json_path[2:], '_') as json_path_key, json_value #>> '{}' as json_value
from json_tree
where json_typeof(json_value) not in ('array', 'object')

O SQL acima deve funcionar para um JSON arbitrário e, neste caso, retorna o seguinte:

|json_path_key       |json_value|
|--------------------|----------|
|value_id            |1         |
|value_desc          |widget    |
|value_isActive      |true      |
|value_colours_0_hue |red       |
|value_colours_0_tint|light     |
|value_colours_1_hue |green     |
|value_colours_1_tint|light     |
|value_colours_2_hue |blue      |
|value_colours_2_tint|dark      |

O SQL recursivo é necessário porque os objetos e matrizes JSON precisam ser descompactados recursivamente. Como a referência recursiva a json_treenão pode aparecer duas vezes na consulta, recorremos a alguns truques nesta etapa, com duas junções à esquerda (das quais apenas uma corresponderá a uma determinada linha) e case ... whenpara garantir json_each()e json_array_elements()não receber tipos de parâmetros incompatíveis , o que resultaria em um erro. (Isso não é muito elegante, estou me perguntando se existe uma maneira melhor de fazer isso.)

O SQL construirá um caminho JSON como um array e, array_to_string()finalmente, produziremos um nome de quase coluna. (Estou omitindo o primeiro elemento do caminho dividindo a matriz com json_path[2:], porque o elemento de nível superior no exemplo JSON é uma matriz, portanto, estritamente falando, as chaves de caminho corretas seriam 0_value_id, 0_value_desc, etc.)

Todos os caminhos JSON são produzidos, mas estamos interessados ​​apenas em nós folha e - como sabemos que matrizes e objetos não podem ser nós folha - precisamos excluí-los no final.

Quanto à segunda pergunta - ou seja, se bem entendi, esta é a melhor maneira de propagar dados JSON arbitrários em um esquema de banco de dados (presumivelmente) correspondente - meu palpite quase certamente não é, embora:

• informações adicionais seriam necessárias para fornecer uma resposta mais confiável, e
• uma solução melhor - seja ela qual for - provavelmente dependerá, pelo menos em parte, do SQL acima