How to put array as field in json. Postgres

Question

I have a json field in PostgreSQL: {"cpu": 88, "hdd": 1200, "ram": 2048}.

I want to add a new array field: spec_os_max = string_to_array('RHEL 7.9,RHEL 8.*,Ubuntu 18.04,ESXi 7.0', ',')

I'm trying this:

update public.resources r
set params = jsonb_set(params::jsonb, '{spec_os_max}', string_to_array('RHEL 7.9,RHEL 8.*,Ubuntu 18.04,ESXi 7.0', ','))
where id in (122561, 122560);

I have the following errors: No function matches the given name and argument types. You might need to add explicit type casts.

Can anybody explain how to do this? Thank You.

You are mixing up PostgreSQL arrays and JSON arrays.

Laurenz Albe
– Laurenz Albe

2022-02-25 13:16:46 +00:00
Commented Feb 25, 2022 at 13:16 — Laurenz Albe
– Laurenz Albe, Commented Feb 25, 2022 at 13:16

Andrew · Accepted Answer · 2022-02-25 13:16:57Z

1

As in Documentation you need to pass to jsonb_set 3 arguments : target jsonb, path text[] and new value as jsonb. You can made it like this:

with resources as (
  select '{"abc": 1, "def": "some text"}'::jsonb as params
)
select jsonb_set(params, array['spec_os_max'], array_to_json(string_to_array('RHEL 7.9,RHEL 8.*,Ubuntu 18.04,ESXi 7.0', ','))::jsonb)
from resources

answered Feb 25, 2022 at 13:16

Andrew

1757 bronze badges

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

Comments

Vesa Karjalainen · Accepted Answer · 2022-02-25 13:17:26Z

1

update t 
set properties = jsonb_set(properties, '{spec}', jsonb_build_array('aa', 'bb'));

answered Feb 25, 2022 at 13:17

Vesa Karjalainen

1,1058 silver badges16 bronze badges

Collectives™ on Stack Overflow

How to put array as field in json. Postgres

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