2

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.

VBAGuy
  • 172
  • 1
  • 9
  • See also https://stackoverflow.com/questions/60112463/postgresql-how-to-update-jsonb-to-add-new-key-in-nested-array/, https://stackoverflow.com/questions/58959678/postgresql-add-key-to-each-objects-of-an-jsonb-array and https://stackoverflow.com/questions/60325238/postgresql-update-a-value-in-a-jsonb-array-column – Bergi Sep 05 '20 at 21:32

1 Answers1

3

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)

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
  • Hi, If i have [ { "database": "Oracle", "company" : "Oracle", "License" : "Proprietary" }, { "database":"Sql Server", "company" :"Microsoft", "License" : "Proprietary" }, { "database":"DB2", "company" :"IBM", "License" : "Proprietary" } ] these type of data then how will modify – Sherin Green Sep 03 '21 at 07:13
  • If I have no table and above is object array contains in a variable, then how will I append a new element to each object in array and store it into a new variable in store procedure. – Sherin Green Sep 03 '21 at 07:18
  • @SherinGreen Do you understand how the code in my answer works? Just omit the parts that deal with the wrapper object if you only want an array. As for modifying variables in a procedure, just use assignment with a subquery or `SELECT INTO`. If it's still unclear, please consider [asking a new question](https://stackoverflow.com/questions/ask). – Bergi Sep 03 '21 at 08:14
  • could you please help me on https://stackoverflow.com/questions/69073612/how-to-append-or-push-serial-number-to-object-in-json-array-in-postgresql – Sherin Green Sep 06 '21 at 12:04