在postgresql中使用jsonb_insert时，无效的令牌错误

Question

作为一点背景。我想用来自其他列的值填充jsonb值的列。最初，我使用这个查询:

UPDATE myTable
SET column_name = 
row_to_json(rowset)
FROM (SELECT column1, column2 FROM myTable)rowset

然而，这个查询似乎在一个有900万条记录的数据集上运行了太长时间(在我停止它之前几个小时)。因此，我寻找第二个FROM子句的解决方案，并找到了jsonb_insert函数。为了测试这个查询，我首先运行了这个示例查询:

SELECT jsonb_insert('{}','{column1}','500000')

给出{'column1':500000}作为输出。完美，所以我尝试使用实际列填充值:

SELECT jsonb_insert('{}':,'{column1}',column1) FROM myTable WHERE id = <test_id>

这给出了一个语法错误，并建议添加参数类型，这导致我如下:

SELECT jsonb_insert('{}':,'{column1}','column1') 
FROM myTable WHERE id = <test_id>
SELECT jsonb_insert('{}'::jsonb,'{column1}'::jsonb,column1::numeric(8,0)) 
FROM myTable WHERE id = <test_id>

这两个查询都给出无效的输入类型语法错误，Token 'column1'是无效的。

我似乎真的找不到这些查询使用文档的正确语法。有人知道正确的语法是什么吗?

Answer 1

因为jsonb_insert函数可能需要为new_value参数使用jsonb类型

jsonb_insert(target jsonb, path text[]， new_value jsonb [， insert_after boolean])

如果我们想获得JSON的数字类型，我们可以尝试将列转换为字符串类型，然后再转换jsonb

如果我们想获得JSON的字符串类型，我们可以尝试使用concat函数带双引号。

CREATE TABLE myTable (column1 varchar(50),column2 int);
INSERT INTO myTable  VALUES('column1',50000);
SELECT jsonb_insert('{}','{column1}',concat('"',column1,'"')::jsonb) as JsonStringType,
jsonb_insert('{}','{column2}',coalesce(column2::TEXT,'null')::jsonb) as JsonNumberType
FROM myTable 

sqlfiddle

注意

如果我们的列值可能为空，我们可以尝试将'null'用于coalesce函数作为coalesce(column2::TEXT,'null')。