Select json column using JPA native query with MemSql

user_299758 0
Viewed 2327

I have a table in a MemSql database which contains a column of type JSON.

I am trying to execute the following query.

select tweet from tweets_json;

The tweet column is the JSON column.

Here is the code I am using to execute this query. 

public List<String> getTweets(){
    Query q = entityManager.createNativeQuery("select tweet from tweets_json");
    List<String> resultList = query.getResultList();
}

I am expecting the result to be a list of strings and each string to represent the JSON.

The problem is that I am getting the string converted to as a single Character object which contains only the first character of the JSON {.

The result is always a list of Character with the open curly bracket symbol regardless the type of the list item I am using.

I tried using List<Object[]>, List<String[]>, List<Object>, List<JsonElement> and all returing the same result.

I even tried to not specify the type of the list elements and return just a List and the result still the same.

How can I get the whole JSON and what is the root cause of this issue?

singlestore jpa spring java
3 Answers

This is what worked out for me.

Use CAST(tweet as CHAR(1000)) in native sql query for typecasting json

Query q = entityManager.createNativeQuery("select CAST(tweet as CHAR(1000)) from tweets_json");

This will return you char[] array which can be cast to String and can be used further.

user_86605530
user_20443020

I'll suggest an alternative approach - you could retrieve the column as a string and thereby not need Hibernate to understand anything about JSON:

Query q = entityManager.createNativeQuery("select tweet :> text from tweets_json");

That's typecasting the JSON field to text, a string datatype (you could also use varchar or whatever as needed).

The result returned is the same (just a string) either way, but this way Hibernate should be able to understand that it's a string.

user_53023870
Related