Need help storing nested json in postgresql using jdbi

Question

I am attempting to store JSON in a postgresql 9.4 database using the JSONB datatype with dropwizard and jdbi. I am able to store the data, but if my json goes any deeper than a single level, the json gets turned into a string instead of nested json.

For instance, the following json

{
  "type":"unit",
  "nested": {
    "key":"embedded"
  }
}

actually gets stored as

{
  "type":"unit",
  "nested":"{key=embedded}"
}

The method signature in my DAO is

@SqlUpdate("insert into entity_json(id, content) values(:id, :content\\:\\:jsonb)")
protected abstract void createJson(@Bind("id") String id, @Bind("content") Map content);

I obviously have something wrong, but I can't seem to figure out the correct way to store this nested data.

Answer 1

You can use PGObject to build a JSONB data type in Java. This way you can avoid any special handling as part of the SQL:

PGobject dataObject = new PGobject();
dataObject.setType("jsonb");
dataObject.setValue(value.toString());

A full example including converting an object to a tree, and using an ArgumentFactory to convert it to a PGobject could look like this:

public class JsonbTest {

    @Test
    public void tryoutjson() throws Exception {
        final DBI dbi = new DBI("jdbc:postgresql://localhost:5432/sighting", "postgres", "admin");
        dbi.registerArgumentFactory(new ObjectNodeArgumentFactor());
        Sample sample = dbi.onDemand(Sample.class);

        ObjectMapper mapper = new ObjectMapper();

        int id = 2;

        User user = new User();
        user.emailaddress = "[email protected]";
        user.posts = 123;
        user.username = "test";

        sample.insert(id, mapper.valueToTree(user));
    }

    public static class User {
        public String username, emailaddress;
        public long posts;
    }

    public interface Sample {
        @SqlUpdate("INSERT INTO sample (id, data) VALUES (:id, :data)")
        int insert(@Bind("id") long id, @Bind("data") TreeNode data);
    }

    public static class ObjectNodeArgumentFactor implements ArgumentFactory<TreeNode> {

        private static class ObjectNodeArgument implements Argument {
            private final PGobject value;

            private ObjectNodeArgument(PGobject value) {
                this.value = value;
            }

            @Override
            public void apply(int position,
                              PreparedStatement statement,
                              StatementContext ctx) throws SQLException {
                statement.setObject(position, value);
            }
        }

        @Override
        public boolean accepts(Class<?> expectedType, Object value, StatementContext ctx) {
            return value instanceof TreeNode;
        }

        @Override
        public Argument build(Class<?> expectedType, TreeNode value, StatementContext ctx) {
            try {
                PGobject dataObject = new PGobject();
                dataObject.setType("jsonb");
                dataObject.setValue(value.toString());
                return new ObjectNodeArgument(dataObject);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

}

Answer 2

I was able to solve this by passing in a string obtained by calling writeValueAsString(Map) on a Jackson ObjectMapper. My createJson method turned into:

@SqlUpdate("insert into entity_json(id, content) values(:id, :content\\:\\:jsonb)")
public abstract void createJson(@Bind("id")String id, @Bind("content")String content);

and I obtained the string to pass in by creating a mapper:

private ObjectMapper mapper = Jackson.newObjectMapper();

and then calling:

mapper.writeValueAsString(map);

This gave me the nested json I was looking for.