Cannot extract mysql's json column using Spring data JPA

Question

Using below SQL in mysql directly, I can get the items I want successfully, saying "['item1', 'item2']".

select JSON_EXTRACT(specific_job, '$.items') from t_job where id = 1;

But when I tried to use that in my Spring project with JPA, like below.

@Query(value = "select JSON_EXTRACT(specific_job, '$.items') from t_job where id = ?1", nativeQuery = true)
String findJobItems(Long jobId);

It's very strange that, when calling this function, the query seems work, but only returns the first letter of the items, like "[".

Is this a bug of spring data JPA? Or is there any better way to extract json fields in Spring data JPA?

Evgeni Dimitrov · Accepted Answer · 2016-07-25 13:38:27Z

2

You have to cast the items as varchar.

CAST(myColumn as CHAR(50))

answered Jul 25, 2016 at 13:38

Evgeni Dimitrov

22.6k34 gold badges124 silver badges148 bronze badges

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

1 Comment

Thanks, this works for me. And I have found that I could cast items to varchar without the char length. CAST(myColumn as CHAR)