CODEWITHSUNDEEP
javajsonpostgresql
Jitendra Verma
Jitendra Verma

Reputation: 9

Convert jsonb postgres column data to String

I have a column of jsonb in Postgres.

Which contains data like "{\" name\":\"xyz\"}". I want to convert it as {"name":"xyz"}. How can I achieve this?

Upvotes: 0

Views: 304

Answers (1)

Ivan Vakhrushev
Ivan Vakhrushev

Reputation: 380

Could you please clarify what exactly you want to do?
If you need to remove whitespaces in Java you can try something like this.

        try (final Connection connection = embeddedPostgres.getTestDatabase().getConnection();
         final Statement statement = connection.createStatement();
         final PreparedStatement updateStatement = connection.prepareStatement("update your_table set your_column = ? where id = ?")) {
        connection.setAutoCommit(false);
        try (final ResultSet resultSet = statement.executeQuery("select * from your_table order by id limit 10")) {
            while (resultSet.next()) {
                final long id = resultSet.getLong("id");
                final String infoAsString = resultSet.getString("info");

                final String withoutWhitespaces = StringUtils.deleteWhitespace(infoAsString);
                final PGobject fixedInfoObject = new PGobject();
                fixedInfoObject.setType("jsonb");
                fixedInfoObject.setValue(withoutWhitespaces);
                updateStatement.setObject(1, fixedInfoObject);
                updateStatement.setLong(2, id);
                updateStatement.executeUpdate();
            }
        }
        connection.commit();
    }

Upvotes: 1

Related Questions