JPQLでJSON型カラムを持つテーブルを検索する

# 概要

MySQL 5.7よりサポートされたJSON型カラムを持つテーブルをJPQLを使って検索するサンプルコードです。
この記事で説明している検索方法はデータベース固有の関数(MySQLのJSON_EXTRACT)を使っています。理想としてはJPA標準機能だけで実現できればいいのですが、いまのところ方法が見つけられなかったので、データベース依存の実装になっています。

**環境**

* Windows10 Professional
* Java 1.8.0_144
* Spring Boot 1.5.6
    * Spring Data JPA
* MySQL 5.7.19

**参考**

* [JPA map JSON column to Java Object] (https://stackoverflow.com/questions/25738569/jpa-map-json-column-to-java-object)


# サンプルコード

## テーブル

サンプルコードで使用するテーブルです。notesというカラムをJSON型にしています。notesに格納するJSONオブジェクトはスキーマレスではなくPOJOとの変換を考えて、下記のcolor, shape, statusという最大3つのフィールドを持ちます。

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

```sql
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;
```

### テストデータ

サンプルコードで使用するテストデータです。

```sql
INSERT INTO stock (name, stocks, notes) VALUES ('うまいぼう', 210, '{"color": "red", "status": "done"}');
INSERT INTO stock (name, stocks, notes) VALUES ('ぽてとふらい', 300, '{"color": "green", "shape": "rectangle"}');
INSERT INTO stock (name, stocks, notes) VALUES ('きなこぼう',  60, '{"color": "blue", "shape": "triangle", "status": "not started"}');
INSERT INTO stock (name, stocks, notes) VALUES ('なまいきびーる', 250, '{"color": "red", "status": "in progress"}');
INSERT INTO stock (name, stocks, notes) VALUES ('ふるーつよーぐる', 930, '{"status": "waiting"}');
INSERT INTO stock (name, stocks, notes) VALUES ('みにこーら',  10, '{"shape": "pentagon", "status": "waiting"}');
INSERT INTO stock (name, stocks, notes) VALUES ('こざくらもち', 330, '{"color": "green", "shape": "rectangle", "status": "not started"}');
INSERT INTO stock (name, stocks, notes) VALUES ('たまごあいす',  20, '{"color": "red", "shape": "hexagon", "status": "waiting"}');
INSERT INTO stock (name, stocks, notes) VALUES ('もちたろう', 100, '{"color": "blue"}');
```

### mysqlクライアントから検索

ネイティブSQLでのJSON型カラムで検索する方法を確認します。

**検索**

```sql
SELECT s.id, s.name, s.notes
  FROM stock AS s
 WHERE JSON_EXTRACT(s.notes, '$.color') = 'red';
+----+-----------------------+-----------------------------------------------------------+
| id | name                  | notes                                                     |
+----+-----------------------+-----------------------------------------------------------+
|  1 | うまいぼう            | {"color": "red", "status": "done"}                        |
|  4 | なまいきびーる        | {"color": "red", "status": "in progress"}                 |
|  8 | たまごあいす          | {"color": "red", "shape": "hexagon", "status": "waiting"} |
+----+-----------------------+-----------------------------------------------------------+
3 rows in set (0.00 sec)
```

```sql
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 | たまごあいす       | {"color": "red", "shape": "hexagon", "status": "waiting"} |
+----+--------------------+-----------------------------------------------------------+
1 row in set (0.00 sec)
```

## アプリケーション

### エンティティクラス

JSON型のnotesカラムに対応するフィールドにPOJOのNotesクラスを指定します。
ただし、このままではエラーになるのでコンバータークラスを指定して値の変換をさせるようにします。

```java
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();
    }

}
```

#### JSON型にマッピングさせるPOJO

JSON型のnotesカラムにマッピングさせるクラスです。lombokのアノテーションを付けているだけで特に実装はありません。

```java
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;
}
```

#### コンバーター

テーブルに格納するJSONオブジェクトとJavaのPOJOとの変換処理に、JPAのAttributeConverterを実装したコンバータークラスを実装します。この内容は参考にあげたページの内容を参考にしました。

```java
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;
    }
}
```

### リポジトリクラス

カスタムメソッドを定義しJPQLを記述します。この記事の冒頭にも書きましたが、データベース固有の関数（この例ではMySQLのJSON_EXTRACT）を使用しています。
このため、リポジトリの単体テストでH2などを使用することができません。

```java
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);
}
```

#### EntityManagerを使う方法

EntityManagerを使うパターンです。JPQLの記述に変更はありません。

```java
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;
}
```

**ちなみに**

DB固有の機能を使わずに下記のようなJPQLが記述できればいいのですが、いまのことろはランタイムエラーになります。

```java: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");
```


## 検索処理の例

```java
@Autowired
private StockRepository repository;

public void findOne() {
    Stock stock = repository.findOne(1L);
    System.out.println(stock);
    // → Stock(id=1, name=うまいぼう, 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=うまいぼう, 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=ぽてとふらい, 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=きなこぼう, 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=なまいきびーる, 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=ふるーつよーぐる, 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=みにこーら, 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=こざくらもち, 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=たまごあいす, 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=もちたろう, 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=うまいぼう, 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=なまいきびーる, 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=たまごあいす, stocks=20, notes=Notes(color=red, shape=hexagon, status=waiting), createAt=2017-09-15T08:20:40, updateAt=2017-09-15T08:20:40, delFlag=false)
}
```

## 更新処理の例

```java
public void save() {
    Notes notes = Notes.builder().color("cyan").status("done").build();
    Stock stock = Stock.builder().name("あんずぼー").stocks(1).notes(notes).delFlag(false).build();
    repository.saveAndFlush(stock);
}
```

mysqlクライアントで確認

```sql
> select * from stock where name = 'あんずぼー'\G
*************************** 1. row ***************************
       id: 14
     name: あんずぼー
   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)
```


## 注意事項

* 冒頭でも書きましたが、データベース依存の実装になっています。（この例ではMySQLのJSON_EXTRACT関数の使用。
* MySQLではJSON型のカラムにインデックスが貼れないので大量データに対する検索ではパフォーマンスに影響がでるかもしれません。