A function for renaming PostgreSQL JSONB keys

I had to do some data wrangling which involved renaming a stack of jsonb keys in PostgreSQL.

This stackoverflow answer gives the correct SQL syntax. As it’s a bit fiddly getting the paths in the right place, I dredged up some PL/pgSQL to write a function I can re-use.

Top tips for writing PL/pgSQL (it’s been a long time): don’t accidentally put tab characters in your script, and remember assignment is := not just plain old equals.

Here’s the function:

CREATE OR REPLACE FUNCTION rename_jsonb_key(data jsonb, basepath text[], oldname text, newname text)
returns jsonb
AS
$$
DECLARE
    oldpath text[];
    newpath text[];
    value jsonb;
    result jsonb;
BEGIN
    IF data #> basepath ? oldname THEN
        oldpath := basepath || oldname;
        newpath := basepath || newname;
        value := data #> oldpath;
        result := jsonb_set(data #- oldpath, newpath, value);
    ELSE
        result := data;
    END IF;
    RETURN result;	
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

and here’s an update which uses it:

 UPDATE my_table 
 SET my_field = rename_jsonb_key(my_field, '{path, to, key}','oldname','newname');

Partial updates of JSON data in Postgres (using JDBI)

With Postgres 9.5 comes the jsonb_set function, for updating a single key within a JSON column. Hooray!

A sample bit of SQL might look like this:

update mytable 
set myfield = jsonb_set(myfield,'{key, subkey}', '"new string value"'::jsonb) 
where id = 5

I’ve put a text value in the example, but the new value can be an entire JSON structure.

I’ve posted previously on using JSON and Postgres with JDBI. To use the jsonb_set function, we need to reuse the BindJson annotation covered in that post. The jsonb_set function also takes an array parameter, defining the path to the key to be set. For this I wrote a new Bind annotation:

@BindingAnnotation(BindTextArray.JsonBinderFactory.class)
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.PARAMETER})
public @interface BindTextArray {
    
    String value();

    public static class JsonBinderFactory implements BinderFactory {

        @Override
        public Binder build(Annotation annotation) {
            return new Binder<BindTextArray, String[]>() {
                
                @Override
                public void bind(SQLStatement sqlStatement, BindTextArray bind, String[] array) {
                    try {
                        String fieldName = bind.value();
                        Connection con = sqlStatement.getContext().getConnection();                        
                        Array sqlArray = con.createArrayOf("text", array);
			sqlStatement.bindBySqlType(fieldName, sqlArray, Types.ARRAY);
                    } catch (SQLException ex) {
                        throw new IllegalStateException("Error Binding Array",ex);
                    }
                }
            };
        }
    }

(Code based on this post: http://codeonthecobb.com/2015/04/18/using-jdbi-with-postgres-arrays/).

Here’s the DAO for the SQL example above, using the new Bind annotation:

 
@SqlUpdate("update mytable set myfield = jsonb_set(myfield, :path,:json) where id = :id")
void updateMyTable(@Bind("id") int id, @BindTextArray("path") String[] path, @BindJson("json") String json)

As you can see, there are limitations to this functionality. We can’t update two different elements in the same JSON column, so if you want to do that, you still need to do it in code. However, the new syntax is handy if you want to update one section of your JSON document, without loading the whole thing into your code base.

Using JDBI with Postgres JSON data

I’ve been migrating some raw JDBC code over to JDBI, and joyfully stripping out lines of boilerplate code for preparing statements, opening record sets, sometimes remembering to close them, handling SQL exceptions which won’t ever occur anyway, and so on. Using the SQL Object API means the only code you have to write is the SQL and a ResultSetMapper to determine how to create your domain objects from the resultset. It really promotes adherence to the single responsibility principle and discourages you from mixing logic in with your database access code.

The database in question has a number of fields containing JSON data. More specifically, they use the PostgreSQL jsonb data type. This has required a little more tinkering to get working.

Inserting jsonb data

Out of the box, JDBI provides two annotations for binding parameters. The @Bind annotation binds a single named argument, and @BindBean binds bean properties with matching names. However, to insert jsonb data, you need to first create an PGobject instance and bind that. To do this, I created a new Binding annotation, following the guidance here: http://jdbi.org/sql_object_api_argument_binding/

The annotation code looks like this:

BindingAnnotation(BindJson.JsonBinderFactory.class)
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.PARAMETER})
public @interface BindJson {
    String value();

    public static class JsonBinderFactory implements BinderFactory {
        @Override
        public Binder build(Annotation annotation) {
            return new Binder<BindJson, String>() {                
                @Override
                public void bind(SQLStatement q, BindJson bind, String jsonString) {
                    try {
                        PGobject data = new PGobject();
                        data.setType("jsonb");
                        data.setValue(jsonString);
                        q.bind(bind.value(), data);                        
                    } catch (SQLException ex) {
                        throw new IllegalStateException("Error Binding JSON",ex);
                    }
                }
            };
        }
    }
}

To use it, annotate the json parameter with the new annotation:

@SqlUpdate("insert into my_table (id,data) VALUES (:id,:data)")
void insertJson(@Bind("id") int id, @BindJson("data") String jsonString);

And that’s it; it just works.

Querying json dynamically

I had a requirement where the parameter supplied to the query was the name of the json element to return. For example, consider the json below. I wanted to be able to paramterise a query to return either one of the key values.

{
   "element": {
      "key1": "value1",
      "key2": "value2",
      "key3": "value3"
   }
}

Using raw JDBC it was possible (although not very pretty) to concatenate a suitable sql statement and then execute it:

String sql = "select data->'element1'->'" + subKeyName + "' as value from mytable"
...

This isn’t possible when the SQL string is specified as a JDBI annotation. I found some useful Postgres json processing functions, including jsonb_extract_path_text which allows you to bind parameters normally:

@SqlQuery("select jsonb_extract_path_text(data,'element1',:subKeyName) as value from mytable")
List<String> getSubKey(@Bind("subKeyName") String subKeyName)

So far I haven’t come across any other issues using JDBI with a PostgreSQL JSON data store. I’m looking forward to trying out the new jsonb functionality in PostgreSQL 9.5 which supports writing partial updates to json fields, yippee!

Super quick Sonar/Postgres setup with docker

Maybe I am easily impressed but wow! Here is how I used docker to setup sonar on my (Ubuntu) laptop superquick.

Postgres

First, set up a postgres container. The command below creates and starts a container called sonar-postgres, using the official docker postgres image.

docker run --name sonar-postgres -e POSTGRES_USER=sonar -e POSTGRES_PASSWORD=secret -d postgres

The container is created containing a sonar database and user with the supplied password. There are various options to the run command, for example to restart the container automatically. See docker run for more details. -d means detach from the container and run it in the background.

The command above does not publish any ports to the host, so we can’t psql to localhost port 5432 to see the database. However, the postgres container does “expose” port 5432 to linked containers. To have a quick peek inside, the following command creates a temporary container which executes the psql command. After entering the password you chose earlier, you are logged into the sonar database. When you exit, the container is gone.

docker run -it --link sonar-postgres:postgres --rm postgres sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U sonar'

An alternative would have been to modify the original run command to include -p 5432:5432 which would publish the ports to the host and allow direct access via psql. This was handy for me when getting started but obviously not ideal in an environment where you might have several postgres containers.

Sonar

The default command to start sonar uses the built in h2 database:

docker run -d --name sonarqube -p 9000:9000 -p 9092:9092 sonarqube:5.1

It’s not good practice to use the embedded database for continued use. To set up a container which uses the postgres container created above, use the following command:

docker run -d --name sonarqube --link sonar-postgres:pgsonar -p 9000:9000 -e SONARQUBE_JDBC_USERNAME=sonar -e SONARQUBE_JDBC_PASSWORD=secret -e SONARQUBE_JDBC_URL=jdbc:postgresql://pgsonar:5432/sonar sonarqube:5.1

In the above command, the –link option links the postgres container with a hostname of “pgsonar”, which is used again in the SONARQUBE_JDBC_URL setting to tell sonar where to find the postgres database.

Tip! If the container won’t start, run the command without the -d switch, so it remains in the foreground and you can see the log output.

Once the container is started, navigate to http://localhost:9000/ and you should see the familiar sonarqube dashboard. This process felt quicker than the usual sonar install done properly, and I can definitely see myself using docker more to supercharge my dev environment!