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

Question

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" 
   }]

user330315 · Accepted Answer · 2020-08-10 13:35:54Z

2

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 ....

answered Aug 10, 2020 at 13:35

user330315

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

Comments

Barbaros Özhan · Accepted Answer · 2020-08-10 17:26:04Z

0

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

answered Aug 10, 2020 at 17:26

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Collectives™ on Stack Overflow

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

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