Select json column using JPA native query with MemSql

Question

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?

There a similar question here Store Retrieve Json data which may help you a lot. — Σωτήρης Ραφαήλ
– Σωτήρης Ραφαήλ, Commented Nov 1, 2018 at 9:00
You tagged your question with mysql, but you mention only MemSQL. If you are not using MySQL, you should remove that tag. — Bill Karwin
– Bill Karwin, Commented Feb 14, 2019 at 19:41

Tamara Koliada · Accepted Answer · 2019-02-19 18:35:42Z

2

+50

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.

edited Feb 19, 2019 at 18:35

Tamara Koliada

1,2042 gold badges16 silver badges31 bronze badges

answered Feb 19, 2019 at 17:54

Sahil SInghal

715 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vlad Mihalcea · Accepted Answer · 2020-02-05 13:31:54Z

0

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.

edited Feb 5, 2020 at 13:31

Vlad Mihalcea

156k85 gold badges599 silver badges984 bronze badges

answered Feb 14, 2019 at 12:21

Alexander Petrov

9,5422 gold badges36 silver badges76 bronze badges

1 Comment

Fanooos Over a year ago

Adding json user type (as adviced here vladmihalcea.com/…) solved my issue.

Jack Chen · Accepted Answer · 2019-02-14 16:58:33Z

0

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.

answered Feb 14, 2019 at 16:58

Jack Chen

1,2141 gold badge7 silver badges11 bronze badges

Collectives™ on Stack Overflow

Select json column using JPA native query with MemSql

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related