Invalid token error when using jsonb_insert in postgresql

Question

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?

D-Shih · Accepted Answer · 2022-05-02 00:31:54Z

2

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

sqlfiddle

Note

if our column value might be null we can try to put 'null' for coalesce function as coalesce(column2::TEXT,'null').

edited May 2, 2022 at 0:31

answered May 1, 2022 at 13:37

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeroen Vermunt Over a year ago

Thanks, this seems to be working. I'll give it a shot on my dataset.

Jeroen Vermunt Over a year ago

Would you perhaps also know how to deal with NULL values in for example the column with number values?

D-Shih Over a year ago

@JeroenVermunt I had edited my asnwer you can try that

Collectives™ on Stack Overflow

Invalid token error when using jsonb_insert in postgresql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related