CODEWITHSUNDEEP
arraysjsonpostgresql
SimonNechypurenko
SimonNechypurenko

Reputation: 11

How to put array as field in json. Postgres

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.

Upvotes: 0

Views: 104

Answers (2)

Vesa Karjalainen
Vesa Karjalainen

Reputation: 1105

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

Upvotes: 1

Andrew
Andrew

Reputation: 175

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

Upvotes: 1

Related Questions