How to convert jsonb value to int and apply expression

[HoneyryderChuck]

Hi 👋 ,

I'm looking to generate a query to apply an atomic increment on a jsonb sql. This can be done with the following expression:

... some_count = jsonb_set(some_count, '{code1}', (COALESCE(some_count->>'code1','0')::int + 1)::text::jsonb, true)

I came to the following sequel, which does not work fully yet:

# pg_json, pg_json_ops loaded
column = Sequel[:table][:some_count].pg_jsonb
# ...
# inside the update clause
, some_count: error_code_column.set(
  "{code1}",
   Sequel::Postgres::JSONBInteger.new(Sequel.function(:coalesce, column["code1"], "0").sql) + 1,
   true
)

Using the above does not work, and blows up with the following error:

"some_count" = jsonb_set("table"."some_count", '{code1}', (coalesce(("table"."some_count" -> 'code1'), '0') + 1), true)
PG::UndefinedFunction: ERROR:  operator does not exist: jsonb + integer
LINE 1: ...aily"."error_code_count" -> 'task_timeout'), '0') + 1), true...
                                                             ^

This happens because the json column lookup isn't converted anymore via ::int (by JSONInteger). What's the correct way to do it in sequel?

Moreover, if I remove the " + 1" part of the update expression, the query runs, but the result is wrong:

Sequel::Postgres::JSONBInteger.new(Sequel.function(:coalesce, column["code1"], "0").sql), # without + 1,
# this appears in the query
... jsonb_set("table"."some_count", '{code1}', '"#<Sequel::SQL::F
unction:0x00007feced2c8aa8>"'::jsonb, true)...

[jeremyevans]

JSONBInteger expects to be initialized with an integer value, not an arbitrary SQL expression. You probably want something like:

Sequel.function(:coalesce, column["code1"], '0').cast_numeric + 1

[HoneyryderChuck]

This produce slightly more verbose SQL than what was expected. So instead of ::int, it applied a CAST(..) AS integer). I guess ::int is just pg syntactic sugar, right?

I've stumbled into a last issue, where the 3rd parameter of json_set needs to be a "jsonb" type, not the integer I am calculating. I've managed to fix it by applying successive cast calls:

(Sequel.function(:coalesce, column["code1"], '0').cast_numeric + 1).cast(:text).cast(:jsonb)

And the query was executed, so success!

Let me know if you still have thoughts about any of this, and thx again for the support!

[jeremyevans]

:: is PostgreSQL specific casting syntax. Sequel's generic casting methods use the SQL standard CAST expression. You are correct that the SQL is a little more verbose.

Your approach looks like a good way to fix it if you cannot modify the schema. FWIW, this JSONB->INTEGER->TEXT->JSONB casting is kind of a smell. I would consider making code1 a regular integer column in such cases, if you have the ability to modify the schema.

[HoneyryderChuck]

The schema could be modified. However, this was done this way because the json column will contain counters for an array of states which will change and extend significantly (we have another column for counting error codes). The tradeoff here is that we won't have to change the schema everytime a new state is used, at the cost of slightly more verbose and inefficient write query.