CODEWITHSUNDEEP
jsonpostgresql
Namrata Waghotkar
Namrata Waghotkar

Reputation: 13

Is there any JSON function in postgreSQL for append new key-value pair in existing JSON array

My existing JSON array looks like this:

[{     "name": "XYZ",
        "Role": "Software Engineer"
    },
    {
        "name": "ABC",
        "Role": "BackEnd Developer"
    }]

Now, If I want to add new key-value as

{ "name": "pqr", "Role": "Tester" }

The output needs to display as

[{     "name": "XYZ",
        "Role": "Software Engineer"
    },
    {
        "name": "ABC",
        "Role": "BackEnd Developer"
    },
    {
         "name": "PQR",
          "Role": "Tester" 
   }]

Upvotes: 1

Views: 420

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65343

If the data type of the column was JSONB instead of JSON, then you could use jsonb_insert() function for DB version 9.6+ within a SELECT statement :

SELECT jsonb_insert( jsdata, 
                     array[jsonb_array_length(jsdata)::text],
                     '{ "name": "pqr", "Role": "Tester" }'::jsonb
                     )
  FROM t
 WHERE ID = <a sample scalar value>

or within an UPDATE statement :

UPDATE t
   SET jsdata = jsonb_insert( jsdata, 
                              array[jsonb_array_length(jsdata)::text],
                             '{ "name": "pqr", "Role": "Tester" }'::jsonb
                            )
 WHERE ID = <a sample scalar value>

Demo

Upvotes: 0

user330315
user330315

Reputation:

It the column only contains the (JSON) array you can use the concatenation operator:

select the_column || '{ "name": "pqr", "Role": "Tester" }'
from the_table;

If you want to actually change the data in the table, use that expression in an UPDATE statement:

update the_table
   set the_column = the_column || '{ "name": "pqr", "Role": "Tester" }'
where ....

Upvotes: 2

Related Questions