Waiting for 9.5 – Additional functions and operators for jsonb

On 12th of May, Andrew Dunstan committed patch:

Additional functions and operators for jsonb
 
jsonb_pretty(jsonb) produces nicely indented json output.
jsonb || jsonb concatenates two jsonb values.
jsonb - text removes a key and its associated value from the json
jsonb - int removes the designated array element
jsonb - text[] removes a key and associated value or array element at
the designated path
jsonb_replace(jsonb,text[],jsonb) replaces the array element designated
by the path or the value associated with the key designated by the path
with the given value.
 
Original work by Dmitry Dolgov, adapted and reworked for PostgreSQL core
by Andrew Dunstan, reviewed and tidied up by Petr Jelinek.

We're getting some more modifier functions/operators for json, that's great.

The description seems to be simple enough to understand, so let's just see it in action:

jsonb_pretty(jsonb)

$ SELECT jsonb_pretty( '{"b":{"c":"d","e":"f"},"g":[1,2,3],"a":1}'::jsonb );
   jsonb_pretty
-------------------
 {                +
     "a": 1,      +
     "b": {       +
         "c": "d",+
         "e": "f" +
     },           +
     "g": [       +
         1,       +
         2,       +
         3        +
     ]            +
 }
(1 ROW)

the pluses on the right above are just psql's way to show that the value is single row, which contains new line characters.

jsonb || jsonb

SELECT '{"a":"b","c":[1,2,3],"d":"e"}'::jsonb || '{"c":[3,4,5],"d":"f","x":"y"}'::jsonb;
                    ?COLUMN?                    
------------------------------------------------
 {"a": "b", "c": [3, 4, 5], "d": "f", "x": "y"}
(1 ROW)

jsonb – text

SELECT '{"a":1,"b":2}'::jsonb - 'a';
 ?COLUMN? 
----------
 {"b": 2}
(1 ROW)

jsonb – int

SELECT '[1,2,3]'::jsonb - 1;
 ?COLUMN? 
----------
 [1, 3]
(1 ROW)

jsonb – text[]

$ SELECT '{"b":{"c":"d","e":"f"},"g":[1,2,3],"a":1}'::jsonb - ARRAY[ 'b', 'c' ];
                 ?COLUMN?                  
-------------------------------------------
 {"a": 1, "b": {"e": "f"}, "g": [1, 2, 3]}
(1 ROW)
 
$ SELECT '{"b":{"c":"d","e":"f"},"g":[1,2,3],"a":1}'::jsonb - ARRAY[ 'g', '1' ];
                     ?COLUMN?                     
--------------------------------------------------
 {"a": 1, "b": {"c": "d", "e": "f"}, "g": [1, 3]}
(1 ROW)

jsonb_replace(jsonb,text[],jsonb)

SELECT jsonb_replace(
    '{"b":{"c":"d","e":"f"},"g":[1,2,3],"a":1}'::jsonb,
    ARRAY[ 'g', '1' ],
    '{"pg":1}'::jsonb
    );
                        jsonb_replace                        
-------------------------------------------------------------
 {"a": 1, "b": {"c": "d", "e": "f"}, "g": [1, {"pg": 1}, 3]}
(1 ROW)

Nice. All looks as expected from the description. JSON support is getting better and better. Thanks guys.