Search code examples
javaspringjpasinglestore

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?

Solution

  • 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.