CODEWITHSUNDEEP
mysqlspringjpaspring-data-jpa
Man Shen
Man Shen

Reputation: 391

Cannot extract mysql's json column using Spring data JPA

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?

Upvotes: 0

Views: 1393

Answers (1)

Evgeni Dimitrov
Evgeni Dimitrov

Reputation: 22506

You have to cast the items as varchar.

CAST(myColumn as CHAR(50))

Upvotes: 2

Related Questions