How to convert text to jsonb entirely for a postgresql column

Question

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 1

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;

Answer 2

Or better, yet, use the operator #>>:

SELECT sec #>> '{sections,a}' FROM test;

And to use this in an expression index you need extra parentheses:

CREATE INDEX foo ON test ((sec #>> '{sections,a}'));

Make sure to use a matching expression (without parentheses) in queries to allow index usage.