SQL Wraps JSON in Quotes for Postgres JSON/JSONB Column types - Breaks Postgres Json Features.

[mphouston]

(Awesome work on Micronaut and Micronaut-Data. We really love it :).

When saving a string field to a Postgres JSON/JSONB column, Micronaut-Data wraps the string in quotes. This prevents the Postgres engine from parsing the value and prevents us from using the Postgres JSON SQL extensions.

This also returns the quotes value on the next read, so we can't parse the JSON using Jackson either.

BTW, I have a workaround, so this is not urgent.

This issue is fixed simplistically by setting the DefaultJdbcRepositoryOperations.jsonCodec to null. Other solutions might be possible. I amusing UTF-8 strings, so there might be some problems with this solution...

Steps to Reproduce

1. Create app for Postgres and Micronaut-JDBC (SQL).
2. Create a Domain/Repository with a json field.
3. Create a record with a JSON string in the field and save() it. For example: {"color": "Blue"}

Expected Behaviour

The JSON is accesible by Postgres SQL extension (e.g. fields->'color'='Blue'). And retrieving the string returns the exact string saved.

Actual Behaviour

The JSON string is saved with with enclosing quotes. This prevents Postgres from parsing this and using it a JSON object.
Also, the string value returned is changed to: "{\"color\": \"Blue\"}".

Environment Information

• Operating System: Windows 10
• Micronaut Version: 2.0.0
• Micronaut Data Version: 1.1.3
• Postgres Version: 12.1
• JDK Version: OpenJDK 14

Example Application

Example app: https://github.com/mphouston/mn-issues/tree/master/ref-issue

Run: ./gradlew test

The test JsonColumnIssueTest is the one that demonstrates this issue.

[mphouston]

Also, this may conflict the fix for #482

[PiotrBaczkowski]

@mphouston what's your workaround?

[mphouston]

My work around is on github (see block on workAround672).

I have a sub-class of the DefaultJdbcRepositoryOperations that adds some features. The work around is to clear the jsonCodec value. That causes it to revert back to just plain string storage. (I know, the work-around is an ugly hack :).

There might be a way to get the DefaultJdbcRepositoryOperations bean and just clear that in your code.

[PiotrBaczkowski]

Sadly, that's not possible in Kotlin or atleast i couldn't find a way to modify jsonCodec to null from child class

[mphouston]

Did you try it with reflection like I did? I don't know Kotlin well, but I think standard Java could get could access the private field with this kind of kludge (not exact syntax, but close enough, I hope) :

  Object bean = . . .;  // Not sure about this one.
  Class clazz = bean.getClass().getSuperclass().getSuperclass();
  Field field2 = clazz.getDeclaredField("jsonCodec");
  field2.setAccessible(true);
  field2.set(bean, null);

It would be ugly, but good enough for the short term.

There might be some other, better way to clear the codec, but I could not find it.

I assume the Micronaut team will make some other option available in the future...

[PiotrBaczkowski]

Will someone be working on this? Or at least can someone give me guidance on how to fix this?

[graemerocher]

Will be looking at data issues next week and catching up, PRs welcome in the meantime. Sorry for the delay.

[BitaliumDev]

Thanks a lot, I've tried to do it by myself but the complexity kills me, after 2 days I still hadn't got idea how and why everything works under the hood and I thought that it's better to leave it to others than fix it by brute force changing. Thanks and have a good day.