Need help storing nested json in postgresql using jdbi

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 = "me@home.com";        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);            }        }    }}

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.