如何将文本完全转换为PostgreSQL列的jsonb格式

Question

我手头有一个Postgresql中的文本列，我想将其转换为JSONB列。

我尝试过以下方法：

1. 创建表test（id serial, sec text, name text）; 2. 插入数据到test表中：（id, sec, name）VALUES (1,'{"gender":"male","sections":{"a":1,"b":2}}','subject'); 3. 通过使用sec::JSONB来改变sec列的类型 ALTER TABLE test ALTER COLUMN sec TYPE JSONB;

这确实将文本列转换成了jsonb格式的列。

但是，如果我查询：

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

我遇到了一个错误。

我发现转换只在一个级别上完成（即：sec->>'sections'可以正常工作）。

查询SELECT pg_typeof(name->>'sections') from test;给出的列类型是文本。

有没有办法将文本完全转换为jsonb，这样我就可以成功地查询SELECT sec->>'sections'->>'a' FROM test;？

我不想像下面这样在查询中将文本转换为json，因为我需要稍后在'a'上创建索引。

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

- Ishwar

Answer 1

操作符->>返回文本。如果你想要jsonb，请使用->。

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

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

用法：

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

- klin

Answer 2

或者更好的方法是使用#>>操作符：

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

而在表达式索引中使用此功能，您需要额外添加括号：

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

确保在查询中使用匹配的表达式（不带括号），以允许索引使用。

- Erwin Brandstetter