[JAVA] Search JPQL for tables with JSON type columns

Overview

This is a sample code that uses JPQL to search a table with JSON type columns supported by MySQL 5.7. The search method described in this article uses a database-specific function (MySQL JSON_EXTRACT). Ideally, it would be good if it could be realized only with JPA standard functions, but since I could not find a method so far, it is a database-dependent implementation.

environment

reference

Sample code

table

This is the table used in the sample code. The column called notes is of JSON type. The JSON object stored in notes has up to 3 fields of color, shape, and status below, considering conversion with POJO instead of schemaless.

{"color": "***", "shape": "***", "status": "***"}

CREATE TABLE IF NOT EXISTS stock (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  stocks INT NOT NULL DEFAULT 0,
  notes JSON,
  create_at DATETIME NOT NULL DEFAULT NOW(),
  update_at DATETIME NOT NULL DEFAULT NOW(),
  del_flag TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
)
DEFAULT CHARSET = UTF8MB4;

test data

Test data used in the sample code.

INSERT INTO stock (name, stocks, notes) VALUES ('Delicious', 210, '{"color": "red", "status": "done"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Potato Furai', 300, '{"color": "green", "shape": "rectangle"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Kinakobo',  60, '{"color": "blue", "shape": "triangle", "status": "not started"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Namaiki beer', 250, '{"color": "red", "status": "in progress"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Furutsuyoguru', 930, '{"status": "waiting"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Minicola',  10, '{"shape": "pentagon", "status": "waiting"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Kozakura Mochi', 330, '{"color": "green", "shape": "rectangle", "status": "not started"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Egg ice',  20, '{"color": "red", "shape": "hexagon", "status": "waiting"}');
INSERT INTO stock (name, stocks, notes) VALUES ('Mochitaro', 100, '{"color": "blue"}');

Search from mysql client

Find out how to search in a JSON type column in native SQL.

** Search **

SELECT s.id, s.name, s.notes
  FROM stock AS s
 WHERE JSON_EXTRACT(s.notes, '$.color') = 'red';
+----+-----------------------+-----------------------------------------------------------+
| id | name                  | notes                                                     |
+----+-----------------------+-----------------------------------------------------------+
|  1 |Delicious| {"color": "red", "status": "done"}                        |
|  4 |Namaiki beer| {"color": "red", "status": "in progress"}                 |
|  8 |Egg ice| {"color": "red", "shape": "hexagon", "status": "waiting"} |
+----+-----------------------+-----------------------------------------------------------+
3 rows in set (0.00 sec)
SELECT s.id, s.name, s.notes
  FROM stock AS s
 WHERE JSON_EXTRACT(s.notes, '$.color', '$.shape') = JSON_ARRAY('red', 'hexagon');
+----+--------------------+-----------------------------------------------------------+
| id | name               | notes                                                     |
+----+--------------------+-----------------------------------------------------------+
|  8 |Egg ice| {"color": "red", "shape": "hexagon", "status": "waiting"} |
+----+--------------------+-----------------------------------------------------------+
1 row in set (0.00 sec)

application

Entity class

Specify the POJO Notes class in the field corresponding to the JSON type notes column. However, if this is left as it is, an error will occur, so specify the converter class to convert the value.

import com.example.domain.converter.JsonNotesConverter;
import com.example.domain.dto.Notes;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;
import java.io.Serializable;
import java.time.LocalDateTime;

@Entity
@Table(name="stock")
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Stock implements Serializable {

    private static final long serialVersionUID = 3766264071895115867L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name="name", nullable = false)
    private String name;
    @Column(name="stocks", nullable = false)
    private Integer stocks;
    @Convert(converter=JsonNotesConverter.class)
    @Column(name="notes")
    private Notes notes;
    @Column(name="create_at", nullable = false)
    private LocalDateTime createAt;
    @Column(name="update_at", nullable = false)
    private LocalDateTime updateAt;
    @Column(name="del_flag", nullable = false)
    private Boolean delFlag;

    @PrePersist
    private void prePersist() {
        createAt = LocalDateTime.now();
        updateAt = LocalDateTime.now();
    }

    @PreUpdate
    private void preUpdate() {
        updateAt = LocalDateTime.now();
    }

}

POJO to map to JSON type

This class maps to JSON type notes column. There is no particular implementation, just annotating lombok.

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Notes {
    private String color;
    private String shape;
    private String status;
}

converter

Implement the converter class that implements JPA's AttributeConverter in the conversion process between the JSON object stored in the table and Java POJO. This content is based on the content of the page I gave as a reference.

import com.example.domain.dto.Notes;
import com.fasterxml.jackson.annotation.JsonInclude;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;

import javax.persistence.AttributeConverter;
import java.io.IOException;

@Slf4j
public class JsonNotesConverter implements AttributeConverter<Notes, String> {

    private final ObjectMapper objectMapper =
            new ObjectMapper().setSerializationInclusion(JsonInclude.Include.NON_NULL);

    @Override
    public String convertToDatabaseColumn(Notes notes) {
        try {
            return objectMapper.writeValueAsString(notes);
        } catch (JsonProcessingException e) {
            log.warn("can't convert to json.", e);
        }
        return null;
    }

    @Override
    public Notes convertToEntityAttribute(String str) {
        try {
            return objectMapper.readValue(str, Notes.class);
        } catch (IOException e) {
            log.warn("can't convert to entity.", e);
        }
        return null;
    }
}

Repository class

Define a custom method and write JPQL. As I wrote at the beginning of this article, I'm using a database-specific function (MySQL JSON_EXTRACT in this example). For this reason, H2 etc. cannot be used in repository unit tests.

import com.example.domain.entity.Stock;
import org.springframework.data.jpa.repository.JpaRepository;

public interface StockRepository extends JpaRepository<Stock, Long>  {
    @Query("SELECT s FROM Stock AS s WHERE FUNCTION('JSON_EXTRACT', s.notes, :key) = :val")
    List<Stock> findByNotes(@Param("key") String key, @Param("val") String val);
}

How to use EntityManager

This is a pattern that uses EntityManager. There is no change in the JPQL description.

public List<Stock> findByNotes() {
    String sql = "SELECT s " +
                 " FROM Stock AS s " +
                 " WHERE FUNCTION('JSON_EXTRACT', s.notes, :key) = :val";

    TypedQuery<Stock> query = entityManager.createQuery(sql, Stock.class)
        .setParameter("key", "$.color")
        .setParameter("val", "red");

    List<Stock> lists = query.getResultList();
    return lists;
}

** By the way **

It would be nice if the following JPQL could be written without using DB-specific functions, but at this point a runtime error will occur.

NG


String sql = "SELECT s FROM Stock AS s WHERE s.notes.color = :color";

TypedQuery<Stock> query = entityManager.createQuery(sql, Stock.class)
    .setParameter("color", "red");

Search process example

@Autowired
private StockRepository repository;

public void findOne() {
    Stock stock = repository.findOne(1L);
    System.out.println(stock);
    // → Stock(id=1, name=Delicious, stocks=210, notes=Notes(color=red, shape=null, status=done), createAt=2017-09-15T08:20:13, updateAt=2017-09-15T08:20:13, delFlag=false)
}

public void findAll() {
    List<Stock> lists = repository.findAll();
    lists.forEach(System.out::println);
    // → Stock(id=1, name=Delicious, stocks=210, notes=Notes(color=red, shape=null, status=done), createAt=2017-09-15T08:20:13, updateAt=2017-09-15T08:20:13, delFlag=false)
    // → Stock(id=2, name=Potato Furai, stocks=300, notes=Notes(color=green, shape=rectangle, status=null), createAt=2017-09-15T08:20:20, updateAt=2017-09-15T08:20:20, delFlag=false)
    // → Stock(id=3, name=Kinakobo, stocks=60, notes=Notes(color=blue, shape=triangle, status=not started), createAt=2017-09-15T08:20:23, updateAt=2017-09-15T08:20:23, delFlag=false)
    // → Stock(id=4, name=Namaiki beer, stocks=250, notes=Notes(color=red, shape=null, status=in progress), createAt=2017-09-15T08:20:27, updateAt=2017-09-15T08:20:27, delFlag=false)
    // → Stock(id=5, name=Furutsuyoguru, stocks=930, notes=Notes(color=null, shape=null, status=waiting), createAt=2017-09-15T08:20:31, updateAt=2017-09-15T08:20:31, delFlag=false)
    // → Stock(id=6, name=Minicola, stocks=10, notes=Notes(color=null, shape=pentagon, status=waiting), createAt=2017-09-15T08:20:33, updateAt=2017-09-15T08:20:33, delFlag=false)
    // → Stock(id=7, name=Kozakura Mochi, stocks=330, notes=Notes(color=green, shape=rectangle, status=not started), createAt=2017-09-15T08:20:36, updateAt=2017-09-15T08:20:36, delFlag=false)
    // → Stock(id=8, name=Egg ice, stocks=20, notes=Notes(color=red, shape=hexagon, status=waiting), createAt=2017-09-15T08:20:40, updateAt=2017-09-15T08:20:40, delFlag=false)
    // → Stock(id=9, name=Mochitaro, stocks=100, notes=Notes(color=blue, shape=null, status=null), createAt=2017-09-15T08:20:43, updateAt=2017-09-15T08:20:43, delFlag=false)
}

public void findByNotes() {
    List<Stock> lists = repository.findByNotes("$.color", "red");
    lists.forEach(System.out::println);
    // → Stock(id=1, name=Delicious, stocks=210, notes=Notes(color=red, shape=null, status=done), createAt=2017-09-15T08:20:13, updateAt=2017-09-15T08:20:13, delFlag=false)
    // → Stock(id=4, name=Namaiki beer, stocks=250, notes=Notes(color=red, shape=null, status=in progress), createAt=2017-09-15T08:20:27, updateAt=2017-09-15T08:20:27, delFlag=false)
    // → Stock(id=8, name=Egg ice, stocks=20, notes=Notes(color=red, shape=hexagon, status=waiting), createAt=2017-09-15T08:20:40, updateAt=2017-09-15T08:20:40, delFlag=false)
}

Example of update process

public void save() {
    Notes notes = Notes.builder().color("cyan").status("done").build();
    Stock stock = Stock.builder().name("Anzubo").stocks(1).notes(notes).delFlag(false).build();
    repository.saveAndFlush(stock);
}

Check with mysql client

> select * from stock where name = 'Anzubo'\G
*************************** 1. row ***************************
       id: 14
     name:Anzubo
   stocks: 1
    notes: {"color": "cyan", "status": "done"}
create_at: 2017-09-15 08:31:48
update_at: 2017-09-15 08:31:48
 del_flag: 0
1 row in set (0.00 sec)

Notes

Recommended Posts

Search JPQL for tables with JSON type columns
MySQL JSON type Value search with SpringBoot + Spring JPA
[Swift / For beginners] Write smartly with type inference
How to search multiple columns with gem ransack
Diffed with JSON