As a little bit of background. I want to fill a column with jsonb values using values from other columns. Initially, I used this query:
UPDATE myTable
SET column_name =
row_to_json(rowset)
FROM (SELECT column1, column2 FROM myTable)rowset
However, this query seems to run for way too long (a few hours before I stopped it) on a dataset with 9 million records. So I looking for a solution with the second FROM clause and found the jsonb_insert function. To test the query I first ran this sample query:
SELECT jsonb_insert('{}','{column1}','500000')
Which gives {'column1':500000} as output. Perfect, so I tried to fill the value using the actual column:
SELECT jsonb_insert('{}':,'{column1}',column1) FROM myTable WHERE id = <test_id>
This gives a syntax error and a suggestion to add argument types, which leads me to the following:
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>
Both these queries give invalid input type syntax error, with Token 'column1' is invalid.
I really can not seem to find the correct syntax for these queries using documentation. Does anyone know what the correct syntax would be?
Because jsonb_insert function might need to use jsonb type for the new_value parameter
jsonb_insert(target jsonb, path text[], new_value jsonb [, insert_after boolean])
if we want to get number type of JSON, we can try to cast the column as string type before cast jsonb
if we want to get a string type of JSON, we can try to use concat function with double-quotes sign.
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
Note
if our column value might be null we can try to put 'null' for coalesce function as coalesce(column2::TEXT,'null').