Ishwar Ishwar - 4 months ago 35
JSON Question

How to convert text to jsonb entirely for a postgresql column

What I have is a text column in Postgresql which I want to convert to JSONB column.

What I have tried is this:


  1. CREATE TABLE test (id serial, sec text, name text);

  2. INSERT INTO test (id, sec, name) VALUES (1,'{"gender":"male","sections":{"a":1,"b":2}}','subject');

  3. ALTER TABLE test ALTER COLUMN sec TYPE JSONB USING sec::JSONB;



This did convert the text column to
jsonb
.

However, if I try to query:

SELECT sec->>'sections'->>'a' FROM test


I get an error.

I see the conversion is done only at one level(i.e: sec->>'sections' works fine).

The query
SELECT pg_typeof(name->>'sections') from test;
gives me column type as text.

Is there a way I can convert the text to jsonb entirely, such that I can query
SELECT sec->>'sections'->>'a' FROM test;
successfully?

I don't want to convert the text to json in the query like below, as I need to create index on 'a' later.

select (sec->>'sections')::json->>'a' from test;

Answer

The operator ->> gives a text as a result. Use -> if you want jsonb:

select 
    pg_typeof(sec->>'sections') a,
    pg_typeof(sec->'sections') b
from test;

  a   |   b   
------+-------
 text | jsonb
(1 row) 

Use:

select sec->'sections'->>'a' 
from test;