Json columns appears as different in WbDataDiff #102

ggsurrel commented 2025年12月08日 10:02:30 +01:00

I am running WbDataDiff on 2 PostgreSQL databases.
The problem is that every JSON/JSONB columns pop up in the diff although they have identical values.

I am running WbDataDiff on 2 PostgreSQL databases. The problem is that every JSON/JSONB columns pop up in the diff although they have identical values.

sql-workbench commented 2025年12月08日 11:57:58 +01:00

As far as JSON is concerned this is somewhat expected as it doesn't provide a canonical storage format. So even if {"foo": 1, "bar": 2} and {"bar":2, "foo":1} are logically equivalent, the comparison is based on a string comparison, which would result in "not equal" (that's also the reason why you can't compare JSON columns using = in Postgres).

JSONB normalizes all values, so that should work. Could provide some test data that shows the problem?

As far as JSON is concerned this is somewhat expected as it doesn't provide a canonical storage format. So even if `{"foo": 1, "bar": 2}` and `{"bar":2, "foo":1}` are logically equivalent, the comparison is based on a string comparison, which would result in "not equal" (that's also the reason why you can't compare JSON columns using `=` in Postgres). JSONB normalizes all values, so that should work. Could provide some test data that shows the problem?

sql-workbench commented 2025年12月10日 08:04:56 +01:00

I tried the following test setup:

create table t1 (id int primary key, data jsonb);
create table t2 (id int primary key, data jsonb);
insert into t1 values (1, '{"foo": 1, "bar": 2}');
insert into t2 values (1, '{"bar": 2, "foo": 1}');
wbdatadiff -referenceTables=stuff.t1 -targetTables=stuff.t2 -file=diff.sql -singleFile=true;

And it correctly identified that both tables contain the same data (i.e. an empty diff.sql)

When changing the data type to json then the two JSON values are indeed considered different, because the string representation is different (it's basically the same as if the column was defined as text)

I tried the following test setup: create table t1 (id int primary key, data jsonb); create table t2 (id int primary key, data jsonb); insert into t1 values (1, '{"foo": 1, "bar": 2}'); insert into t2 values (1, '{"bar": 2, "foo": 1}'); wbdatadiff -referenceTables=stuff.t1 -targetTables=stuff.t2 -file=diff.sql -singleFile=true; And it correctly identified that both tables contain the same data (i.e. an empty diff.sql) When changing the data type to `json` then the two JSON values are indeed considered different, because the string representation is different (it's basically the same as if the column was defined as `text`)

ggsurrel commented 2025年12月11日 14:36:01 +01:00

I cannot reproduce the behavior you describe. On my environment, JSONB columns are always considered different.

I reproduced your test setup and launched the following command

WbDataDiff
 -file=diff.sql
 -singleFile=true
 -referenceConnection="username=***,password=***,url=jdbc:postgresql://localhost:5432/dev,driver=org.postgresql.Driver,driverjar=/.../postgresql-42.7.3.jar"
 -targetConnection="username=***,password=***,url=jdbc:postgresql://localhost:5432/dev,driver=org.postgresql.Driver,driverjar=/.../postgresql-42.7.3.jar"
 -includeDelete
 -referenceTables=public.t1
 -targetTables=public.t2;

And I have the following result file

-- No INSERTs for t2 necessary
-- -------------------
-- UPDATES for public.t2
-- -------------------
UPDATE t2
 SET data = CAST('{"bar": 2, "foo": 1}' AS JSONB)
WHERE id = 1;
COMMIT;
-- No DELETEs for t2 necessary

This the result even after running the proposed update or when the initial JSONB string is identical.

Postgresql server version used is 17.5.

I cannot reproduce the behavior you describe. On my environment, JSONB columns are always considered different. I reproduced your test setup and launched the following command ``` WbDataDiff -file=diff.sql -singleFile=true -referenceConnection="username=***,password=***,url=jdbc:postgresql://localhost:5432/dev,driver=org.postgresql.Driver,driverjar=/.../postgresql-42.7.3.jar" -targetConnection="username=***,password=***,url=jdbc:postgresql://localhost:5432/dev,driver=org.postgresql.Driver,driverjar=/.../postgresql-42.7.3.jar" -includeDelete -referenceTables=public.t1 -targetTables=public.t2; ``` And I have the following result file ``` -- No INSERTs for t2 necessary -- ------------------- -- UPDATES for public.t2 -- ------------------- UPDATE t2 SET data = CAST('{"bar": 2, "foo": 1}' AS JSONB) WHERE id = 1; COMMIT; -- No DELETEs for t2 necessary ``` This the result even after running the proposed update or when the initial JSONB string is identical. Postgresql server version used is 17.5.

ggsurrel commented 2025年12月11日 14:52:44 +01:00

This seems to be an issue with sqlwbconsole. I ran the exact same command in sqlworkbench and everything seems to be working as expected.

This seems to be an issue with `sqlwbconsole`. I ran the exact same command in `sqlworkbench` and everything seems to be working as expected.

sql-workbench commented 2025年12月11日 18:45:41 +01:00

Can you please check that you are using the same JDBC driver (version) in both environments?

Can you please check that you are using the same JDBC driver (version) in both environments?

ggsurrel commented 2025年12月12日 11:50:12 +01:00

I am using an absolute URL, so I am pretty sure of it, yes. I am using postgresql-42.7.3 JDBC driver.

I am using an absolute URL, so I am pretty sure of it, yes. I am using `postgresql-42.7.3` JDBC driver.

sql-workbench commented 2025年12月12日 20:20:23 +01:00

Turns out, it depends on how the JDBC driver is loaded. The way you use it, the driver gets loaded twice and the classes that the driver returns for JSON and JSONB columns don't work properly if the values are loaded through two different class loaders.

I could find a workaround for that. Thanks for reporting.

Turns out, it depends on how the JDBC driver is loaded. The way you use it, the driver gets loaded twice and the classes that the driver returns for JSON and JSONB columns don't work properly if the values are loaded through two different class loaders. I could find a workaround for that. Thanks for reporting.