Postgres query - update field in json column with text type

Question

I have a column in my postgres DB called metadata which stores a JSON string, and the type is TEXT.

I'm trying to run a query to update a field named myCount inside the JSON. I'm using Spring Boot and JDBC.

String query = "UPDATE " + mTableName + " SET metadata = jsonb_set(metadata::jsonb, '{myCount}', ?)::text" +
                " WHERE scope = ?";
        PreparedStatement preparedStmt = jdbcTemplate.getDataSource().getConnection().prepareStatement(query);
        preparedStmt.setInt   (1, myCount);
        preparedStmt.setString(2, scope);

        // execute the java preparedstatement
        return preparedStmt.executeUpdate();

I got the following error: ERROR: function jsonb_set(jsonb, unknown, integer) does not

Any ide ahow I can run a query that updates the myCount column inside the JSON?

@a_horse_with_no_name still issue - ` ERROR: function jsonb_set(jsonb, text[], integer) does not exist `, about the jsonb, becuase of the in memory database that does not support json in tests — TheUnreal
– TheUnreal, Commented Mar 17, 2020 at 16:23
"because of the in memory database that does not support json in tests" which means you will never be able to test if you store valid JSON in the database. I am also pretty sure that this in-memory database also doesn't support jsonb_set(), so how are you going to test this code? — user330315
– user330315, Commented Mar 17, 2020 at 16:27
@a_horse_with_no_name Let's focus on the issue, I have the same error when the column is json(function jsonb_set(jsonb, text[], integer) does not exist) — TheUnreal
– TheUnreal, Commented Mar 17, 2020 at 17:01

user330315 · Accepted Answer · 2020-03-17 17:14:17Z

1

function jsonb_set(jsonb, unknown, integer) does not

Tells you that you are trying to call the function with an integer value as the last parameter. But the function is defined as jsonb_set(jsonb, text[], jsonb) so you will need to convert the integer value to a JSONB value:

SET metadata = jsonb_set(metadata::jsonb, '{myCount}'::text[], to_jsonb(?))::text"

answered Mar 17, 2020 at 17:14

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres query - update field in json column with text type

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related