CODEWITHSUNDEEP
javaspringjpasinglestore
Fanooos
Fanooos

Reputation: 2818

Select json column using JPA native query with MemSql

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?

Upvotes: 4

Views: 3082

Answers (3)

Alexander Petrov
Alexander Petrov

Reputation: 9492

You need to use JSON_EXTRACT_STRING function.

select JSON_EXTRACT_STRING(tweet,0) from tweets_json;

The exact functiona definition is:

JSON_EXTRACT_<type>(json, keypath)

You can find examples here: https://docs.memsql.com/sql-reference/v6.7/json_extract_type/

and here

Reading JSON from MEMSQL

With respect to the common case when EntityManager/HibernateSession is used, a custom Hibernate Type is necessary.

Upvotes: 0

Sahil SInghal
Sahil SInghal

Reputation: 71

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.

Upvotes: 2

Jack Chen
Jack Chen

Reputation: 1214

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.

Upvotes: 0

Related Questions