Transformando valores de array HSTORE em uma coluna em JSONB

Question

Estou tentando transformar meus dados armazenados na coluna HSTORE ('data') do Postgres.

Meus valores de linha têm a chave "entidade" e o valor está na matriz.

"entity"=>"[{'id': .............}]

Eu usei o seguinte código:

Alter TABLE my_table 
   ALTER COLUMN h_store_column TYPE jsonb 
   USING hstore_to_jsonb_loose(data -> 'entity');

que resultou em valor como saída em uma nova coluna conforme abaixo:

"[{'id': .............}]"

mas, com aspas "". Isso tornou escalar na coluna do tipo JSONB e não me permite executar a consulta.

Como posso alterar o valor de cada linha em uma nova coluna chamada 'entidade' com JSONB, sem aspas?

[{'id': .............}]

CÓDIGO DE AMOSTRA PARA GERAR DADOS SEMELHANTES:

"key" => "[json_text_array]"

armazenados na hstorecoluna de tipo de dados.

Quando alterado para o tipo JSON B, recebo {'key':'[array]'}, enquanto estou atrás {'key': [array]}- Sem aspas. Tentei funções soltas no postgres, sem ajuda.

Answer 1

De acordo com sua pergunta, o que eu entendi, você tem uma coluna com tipo hstorecom uma chave nomeada entitye valor como JSON ARRAY. A explicação do seu problema e solução será como:

1. Sua Alterconsulta mencionada na pergunta será por erro porque a hstore_to_jsonb_loosefunção aceita hstoreo valor do tipo, mas você está passando text. Portanto, a instrução correta para a consulta deve ser.

    Alter TABLE my_table 
       ALTER COLUMN h_store_column TYPE jsonb 
       USING hstore_to_jsonb_loose(data) -> 'entity';

1. A consulta acima converterá o hstorevalor-chave em um par de valor-chave jsonb e o atualizará na coluna h_store_column.

Portanto, a função hstore_to_jsonb_looseconverterá os dados a partir { "entity": "[{'id':..........}]" }dos quais você está extraindo o JSONvalor da chave 'entity'que é "[{'id':..........}]".

1. Você deseja armazenar seu valor buscado hstore_to_jsonb_loose(data) -> 'entity'como full JSON ARRAY. Seus dados armazenados como valor na hstorecoluna de tipo parecem um JSON, mas não são um JSON. Em JSON, chaves e valores (que não sejam numéricos e booleanos) são cercados por, "mas em sua string ele é cercado por '. Portanto, não pode ser armazenado como JSONna coluna do tipo JSONB .

2. Considerando que não há outro problema na estrutura de valores como JSON (além de '). Devemos substituir 'por "e armazenar o valor como JSONB na coluna. Tente esta consulta para fazer o mesmo.

Alter TABLE test 
   ALTER COLUMN h_store_column TYPE jsonb 
   USING replace(hstore_to_jsonb_loose(data)->>'entity','''','"')::jsonb;

DEMO1

Mesmo hstore_to_jsonb_loosenão é necessário no seu caso. Você pode escrever sua Alterdeclaração como abaixo:

Alter TABLE test 
   ALTER COLUMN h_store_column TYPE jsonb 
   USING replace((data)->'entity','''','"')::jsonb;

DEMO2