Trasformazione dei valori dell'array HSTORE in una colonna in JSONB

Question

Sto cercando di trasformare i miei dati memorizzati nella colonna HSTORE ("dati") di Postgres.

I miei valori di riga hanno la chiave "entità" e il valore è nell'array.

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

Ho usato il seguente codice:

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

che ha prodotto il valore come output in una nuova colonna come di seguito:

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

ma, con le virgolette "". Questo lo ha reso scalare nella colonna di tipo JSONB e non mi consente di eseguire la query.

Come posso modificare il valore di ogni riga in una nuova colonna denominata "entità" con JSONB, senza virgolette?

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

CODICE DI ESEMPIO PER GENERARE DATI SIMILI:

"key" => "[json_text_array]"

memorizzato nella hstorecolonna del tipo di dati.

Quando sono passato al tipo JSON B, ottengo {'key':'[array]'}, mentre sono dopo {'key': [array]}- Senza virgolette. Ho provato funzioni sciolte in postgres, nessun aiuto.

Answer 1

Secondo la tua domanda, cosa ho capito, hai una colonna con tipo hstorecon una chiave denominata entitye valore come JSON ARRAY. La spiegazione del problema e della soluzione sarà la seguente:

1. La tua Alterquery menzionata nella domanda sarà per errore perché hstore_to_jsonb_loosela funzione accetta il hstorevalore del tipo ma stai passando text. Quindi l'istruzione corretta per query dovrebbe essere.

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

1. La query sopra convertirà il hstorevalore-chiave nella coppia valore-chiave jsonb e lo aggiornerà nella colonna h_store_column.

Quindi la funzione hstore_to_jsonb_looseconvertirà i dati { "entity": "[{'id':..........}]" }da cui stai estraendo il JSONvalore della chiave 'entity'che è "[{'id':..........}]".

1. Vuoi memorizzare il tuo valore recuperato da hstore_to_jsonb_loose(data) -> 'entity'as full JSON ARRAY. I tuoi dati memorizzati come valore nella hstorecolonna di tipo sembrano un JSONma non è un file JSON. In JSON, chiavi e valori (diversi da numerici e booleani) sono circondati da "ma nella tua stringa è circondato da '. Quindi non può essere memorizzato come JSONnella colonna di tipo JSONB .

2. Considerando che non ci sono altri problemi nella struttura dei valori come JSON (diversi da '). Dovremmo sostituire 'con "e memorizzare il valore come JSONB nella colonna. Prova questa query per fare lo stesso.

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

DEMO1

Anche hstore_to_jsonb_loosenon è richiesto nel tuo caso. Puoi scrivere la tua Alterdichiarazione come di seguito:

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

DEMO2