Postgresql update json data with variables

Question

I am trying to perform an update with a cursor variable in a previous step, but in a jsonb field, the system returns an error. Contend field is a jsonb type. I am doing it as follows:

¿Can you help me please?

Update OPTION 1

update test
set
content = jsonb_set(content, '{supplier}', '' || supplierId || ''::jsonb)
where operation_type = 'DEL';

Error OPTION 1

SQL Error [22P02]: ERROR: invalid input syntax for type json Detail: The input string ended unexpectedly. Where: JSON data, line 1: PL/pgSQL function inline_code_block line 72 at SQL statement

Update OPTION 2

update test
set
content = jsonb_set(content, '{supplier}', '"' || supplierId || '"'::jsonb)
where operation_type = 'DEL';

Error OPTION 2

SQL Error [22P02]: ERROR: invalid input syntax for type json Detail: Token """ is invalid. Where: JSON data, line 1: " PL/pgSQL function inline_code_block line 72 at SQL statement

Kaushik Nayak · Accepted Answer · 2019-07-25 09:58:40Z

2

First, cast the integer column to text and then to jsonb

jsonb_set(content, '{supplier}',  
          supplierId  :: text :: jsonb)

DEMO

answered Jul 25, 2019 at 9:58

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Comments

jou gr · Accepted Answer · 2019-07-25 11:33:11Z

0

Thank you very much for your help. For Strings works perfectly, but numeric values show an error message.

content = jsonb_set(content, '{supplier}', supplierId::numeric::jsonb)

Error: SQL Error [42846]: ERROR: cannot cast type numeric to jsonb Where: PL/pgSQL function inline_code_block line 65 at SQL statement

Is there another way to convert it?

Example data:

'{"supplier": 1,"name": "Josh", "supplierId":10}'

Thank you very much,

EDIT: I resolved with: content = jsonb_set(content, '{supplier}', supplierId::integer::text::jsonb)

edited Jul 25, 2019 at 11:33

answered Jul 25, 2019 at 11:04

jou gr

111 silver badge3 bronze badges

Collectives™ on Stack Overflow

Postgresql update json data with variables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related