CODEWITHSUNDEEP
jsonpostgresql
VBAGuy
VBAGuy

Reputation: 172

Postgresql : Append element in json array objects

I have below jsonb data stored in table column as below:

'{"info":[
    {
        "database": "Oracle",
        "company" : "Oracle"
    },
    {
        "database":"Sql Server",
        "company" :"Microsoft"
    },
    {
        "database":"DB2",
        "company" :"IBM"
    }
]}'

i need to append a element "License" : "Proprietary" to all objects present in json array like below:

'{"info":[
    {
        "database": "Oracle",
        "company" : "Oracle",
        "License" : "Proprietary"
    },
    {
        "database":"Sql Server",
        "company" :"Microsoft",
        "License" : "Proprietary"
    },
    {
        "database":"DB2",
        "company" :"IBM",
        "License" : "Proprietary"
    }
]}'

i have added the sql fiddle link here: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=3990e79140df1c897aac2fb19364d2e9

Thanks in advance.

Upvotes: 2

Views: 302

Answers (1)

Bergi
Bergi

Reputation: 664764

You need to call set_jsonb on each array element individually, getting the values with jsonb_array_elements and aggregating them back into a json array with jsonb_agg:

UPDATE test
SET data = jsonb_set(data, '{info}', (
  SELECT jsonb_agg(el || '{"License" : "Proprietary"}')
  FROM jsonb_array_elements(data -> 'info') el
));

(Updated dbfiddle demo)

Upvotes: 3

Related Questions