Some new functions and functions of the JSONB data type in PostgreSQL version 9.5

Jsonb-modifying Operators and functions

In 9.3 (and to a greater extent in 9.4), JSONB data could is extracted using various functions and operators, but nothing That could actually modify the data. As of 9.5, JSONB data can now is modified.
jsonb | | JSONB (Concatenate/overwrite)

the | | operator allows us to combine 2 jsonb objects. If there ' s overlap, values is replaced on the highest level.

For example, if we want to add values to a Jsonb object:

# select ' {' name ': ' Joe ', ' age ': $ ':: jsonb | | ' {' Town ': ' London '} ':: Jsonb;
? column?
----------------------------------------------
{"Age": +, "name": "Joe", "Town": "London"}
(1 row)


Or We can overwrite existing values:

# select ' {' Town ': ' Dataville ', ' Population ': 4096} ':: Jsonb | | ' {' Population ': 8192} ':: Jsonb;
? column?
-------------------------------------------
{"Town": "Dataville", "Population": 8192}
(1 row)

Note that this is only works on the highest level and so nested objects is replaced from the top level. For example:

# select ' {"name": "Jane", "contact": {"Phone": "01234 567890", "mobile": "07890 123456"}} ':: Jsonb | | ' {' contact ': {' fax ': ' 01987 654321 '} ':: Jsonb;
? column?
------------------------------------------------------
{"Name": "Jane", "contact": {"Fax": "01987 654321"}}
(1 row)


Jsonb-text/int (remove Key/array Element)

We can remove keys from a Jsonb object with The-operator:

# select ' {"name": "James", "email": "[email protected]"} ':: jsonb-' email ';
? column?
-------------------
{"Name": "James"}
(1 row)

Or remove values from an array (base 0):

# SELECT ' [' Red ', ' green ', ' Blue '] ':: jsonb-1;
? column?
-----------------
["Red", "blue"]
(1 row)

jsonb-text[]/int (remove Key/array element in path)

The previous example, we can remove keys or array elements, but no lower than the highest level, so we can provide a Path to the value we want to delete using a text array. Here we'll want to remove the fax number from within the contact value:

# select ' {"name": "James", "contact": {"Phone": "01234 567890", "Fax": "01987 543210"}} ':: Jsonb-' {contact,fax} ':: text[] ;
? column?
---------------------------------------------------------
{"Name": "James", "contact": {"Phone": "01234 567890"}}
(1 row)

Or we can remove an array value. Here we ll get rid of the array value as index 1 (2nd value):

# select ' {"name": "James", "aliases": ["Jamie", "The Jamester", "J Man"]} ':: Jsonb-' {aliases,1} ':: text[];
? column?
--------------------------------------------------
{"Name": "James", "aliases": ["Jamie", "J Man"]}
(1 row)

Jsonb_replace function

The above lets us delete values in a path, and not update them, so we had the Jsonb_replace function for that. WE ll update the phone value within the contact value:

# SELECT Jsonb_replace (' {' "name": "James", "contact": {"Phone": "01234 567890", "Fax": "01987 543210"}} ':: Jsonb, ' {Contact , phone} ', ' "07900 112233" ':: JSONB);
Jsonb_replace
--------------------------------------------------------------------------------
{"Name": "James", "contact": {"Fax": "01987 543210", "Phone": "07900 112233"}}
(1 row)

Jsonb_pretty

Notice that jsonb doesn ' t preserve white-space, so no matter what effort you went into order to make the object easier to Read, it'll end up as a long string. Well jsonb_pretty would format it for you. If we use the previous JSONB example and wrap it all in a jsonb_pretty function:

# SELECT Jsonb_pretty (Jsonb_replace (' {' name ': "James", "contact": {"Phone": "01234 567890", "Fax": "01987 543210"}} ':: Jsonb, ' {contact,phone} ', ' "07900 112233" ':: jsonb) ";
Jsonb_pretty
---------------------------------
{                              +
"Name": "James", +
"Contact": {+
"Fax": "01987 543210", +
"Phone": "07900 112233" +
}                          +
}
(1 row)

Much easier to read.

Some new functions and functions of the JSONB data type in PostgreSQL version 9.5