PostgreSQLのjsonb型の処理関数を知り配列の検索をできるようになるまで

開発しているアプリケーションで複雑なJsonに対して曖昧検索したいことがあり、Jsonb型に対するクエリを学んだので、少しずつステップアップして見ていきます。



# ゴール
複雑なjsonbの検索ができるようになる。

具体的には、以下のような「Jsonの中の、配列の中の、jsonの特定のキー」に対しての検索ができるようになりたいです。
例えば、「Draft.js」と言う単語で曖昧検索をするクエリが書けるようになれば完璧です！

```json
{
  "blocks": [
    {
      "key": "ed578",
      "text": "このEditorにはDraft.jsというのを利用しています。",
      "type": "unstyled",
      "depth": "0",
      "entity_ranges": [
        ""
      ],
      "inline_style_ranges": [
        ""
      ]
    },
    {
      "key": "fbt46",
      "text": "リンクは自動で変わります。",
      "type": "unstyled",
      "depth": "0",
      "entity_ranges": [
        ""
      ],
      "inline_style_ranges": [
        ""
      ]
    }
  ]
}
```

以下は、contentカラムのjsonbのblocksの中の、配列にあるtextに「Draft.js」が含まれているデータを取得するクエリの例です。
```sql
SELECT posts.*
FROM posts, JSONB_ARRAY_ELEMENTS(content->'blocks') block
WHERE block->>'text' LIKE '%Draft.js%';
```
この記事を読み終えた時に、このクエリがわかるようになっているといいなと思います。


## jsonbのデータの準備

### jsonbとは
ドキュメントにはjsonb型について以下のように書いてあります。
> json型とjsonb型というデータ型は、ほとんど 同一の入力値セットを受け入れます。 現実的に主要な違いは効率です。 


> 一般的に、ほとんどのアプリケーションではJSONデータ型としてjsonb型のほうが望ましいでしょう。

参考: https://www.postgresql.jp/document/15/html/datatype-json.html


### テーブル作成

テーブルは以下のような構造とします。

```sql
CREATE TABLE Posts (
  id SERIAL PRIMARY KEY,
  content JSONB NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
```


# Step1: Jsonの処理関数を使ってみる

PostgreSQLにはJsonの処理するための関数がいくつか存在します。
ここでは、まずその中の一部を紹介してみます。

## サンプルデータ

```sql
INSERT INTO Posts (content) VALUES 
('{
  "name": "フグ田 サザエ",
  "age": 24,
  "profession": "主婦",
  "hobbies": ["料理", "家事", "ショッピング"]
}'::jsonb),
('{
  "name": "磯野 波平",
  "age": 54,
  "profession": "無職",
  "hobbies": ["釣り", "新聞読む", "寝る"]
}'::jsonb),
('{
  "name": "磯野 フネ",
  "age": 50,
  "profession": "主婦",
  "hobbies": ["家事", "散歩"]
}'::jsonb),
('{
  "name": "フグ田 マスオ",
  "age": 28,
  "profession": "サラリーマン",
  "hobbies": ["釣り", "ゴルフ", "読書"]
}'::jsonb);
````

## jsonb_array_length関数

jsonbの配列の長さを返してくれます。
以下は、各データのidと対応するhobbiesの要素数を取得するクエリです。
```sql
SELECT id, jsonb_array_length(content->'hobbies') AS hobbies_count
FROM posts;
````
![スクリーンショット 2023-07-15 14.08.52.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/107345/515b35fd-647e-236d-4557-a95a9bb35b60.png)


## jsonb_each関数
jsonbのkeyとvalueを返す関数です。
以下はidとそのcontentに含まれるjsonのkey, valueを取得するクエリです。
```sql
SELECT id, key, value
FROM posts, jsonb_each(content);
```
![スクリーンショット 2023-07-15 14.11.54.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/107345/38e66916-8968-4915-79e4-103bb581c89d.png)



## jsonb_typeof関数
指定したキーに対応する値の型を返します。
```sql
SELECT
  jsonb_typeof(content->'name') AS name_type,
  jsonb_typeof(content->'age') AS age_type,
  jsonb_typeof(content->'profession') AS profession_type,
  jsonb_typeof(content->'hobbies') AS hobbies_type
FROM posts;
```
![スクリーンショット 2023-07-15 14.15.36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/107345/6c9c97e0-f556-a5f4-e144-0c23f2829f4a.png)


## json_array_elements関数
指定したjson配列内のすべての要素をJSON値として返します。

```sql
SELECT posts.id, hobby
FROM posts, JSONB_ARRAY_ELEMENTS(content->'hobbies') hobby;
```

![スクリーンショット 2023-07-15 14.24.08.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/107345/2d4becfe-5c23-e415-e18c-6c1ff49cd0d3.png)

## json_array_elements_text
指定したjson配列内のすべての要素をTEXT値として返します。

以下は釣りが趣味な人を返すクエリです。
```sql
SELECT posts.* FROM posts, jsonb_array_elements_text(content->'hobbies') hobby
WHERE hobby = '釣り';
```
![スクリーンショット 2023-07-15 14.42.36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/107345/5d920b7a-fbbf-2925-a708-4f3db6d4b0b1.png)


# STEP2: 単純なjsonに対して検索してみる

まずはの準備をします。

### データinsert
contentカラムには以下のようなデータがあるとします。

```json
{
  "name": "フグ田 サザエ",
  "age": 24,
  "profession": "主婦"
}
```
```json
{
  "name": "磯野 波平",
  "age": 54,
  "profession": "無職"
}
```
```json
{
  "name": "磯野 フネ",
  "age": 50,
  "profession": "無職"
}
```
```json
{
  "name": "フグ田 マスオ",
  "age": 28,
  "profession": "サラリーマン"
}
```
```json
{
  "name": "磯野 カツオ",
  "age": 11,
  "profession": "小学生"
}
```
```json
{
  "name": "磯野 ワカメ",
  "age": 9,
  "profession": "小学生"
}
```
```json
{
  "name": "フグ田 タラオ",
  "age": 3,
  "profession": "幼稚園児"
}
```
```json
{
  "name": "中嶋",
  "age": 11,
  "profession": "小学生",
}
```
```json
{
  "name": "花沢 花子",
  "age": 11,
  "profession": "小学生",
}
```

<details><summary>Insert文</summary><div>


```sql
INSERT INTO Posts (content) VALUES 
('{
  "name": "フグ田 サザエ",
  "age": 24,
  "profession": "主婦"
}'::jsonb),
('{
  "name": "磯野 波平",
  "age": 54,
  "profession": "無職"
}'::jsonb),
('{
  "name": "磯野 フネ",
  "age": 50,
  "profession": "無職"
}'::jsonb),
('{
  "name": "フグ田 マスオ",
  "age": 28,
  "profession": "サラリーマン"
}'::jsonb),
('{
  "name": "磯野 カツオ",
  "age": 11,
  "profession": "小学生"
}'::jsonb),
('{
  "name": "磯野 ワカメ",
  "age": 9,
  "profession": "小学生"
}'::jsonb),
('{
  "name": "フグ田 タラオ",
  "age": 3,
  "profession": "幼稚園児"
}'::jsonb),
('{
  "name": "中嶋",
  "age": 11,
  "profession": "小学生"
}'::jsonb),
('{
  "name": "花沢 花子",
  "age": 11,
  "profession": "小学生"
}'::jsonb);
```

</div></details>

## jsonの中で特定のフィールドのみを取得してみる

```json
{
  "name": "フグ田 サザエ",
  "age": 24,
  "profession": "主婦"
}
```
このデータのうち、まずはname一覧だけ取得するクエリを書いてみます。

`(カラム名) ->> '(フィールド名)'`という表現を使います。

```sql
SELECT content ->> 'name' AS name FROM posts;
```

他には以下のような書き方もできます。
```sql
SELECT content #> '{name}' AS name FROM posts;
```



![スクリーンショット 2023-07-08 15.09.24.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/107345/d7b49f6a-98ca-3f0b-c578-410c2bb9da4d.png)


## jsonの中で特定のフィールドのみを検索対象にしてみる
先ほどのクエリで `(カラム名) ->> '(フィールド名)'`を学びました。
これを用いて、「age = 11」の人だけを検索をしてみます。

```sql
SELECT * FROM posts WHERE content ->> 'age' = '11';
```

![スクリーンショット 2023-07-08 15.13.58.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/107345/fdb44105-eed9-58db-3b7c-dee393ce14f9.png)

できました！

## 特定のフィールドのみで曖昧検索
では次に曖昧検索もしてみましょう！

nameに「磯野」を含むデータを検索してみます！

```sql
SELECT * FROM posts WHERE content ->> 'name' LIKE '%磯野%';
```

![スクリーンショット 2023-07-08 15.16.26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/107345/8c519e77-6e1c-69ee-65ad-52017a6b9212.png)

できていますね！

では次に「磯野」家の中で、小学生の人のみを検索してみます！

## 複数のフィールドを検索対象にしてみる

磯野家の小学生は、「磯野 カツオ」と「磯野 ワカメ」です。

複数フィールドなので WHERE文にANDを組み合わせたクエリを作ります。
```sql
SELECT *
FROM posts
WHERE content ->> 'name' LIKE '%磯野%'
AND content ->> 'profession' = '小学生';
```

![スクリーンショット 2023-07-08 15.22.19.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/107345/c3e8ae76-756b-032d-aecd-d6b8670087bc.png)

nameに「磯野」を含む かつ professionが「小学生」と一致する データが取得できました！

# STEP3: 次はネストされたjson...！

ネストされたjsonのレコード例は以下です。
```json
{
  "character": {
    "name": "フグ田 サザエ",
    "age": 24,
    "profession": "主婦",
    "family": {
      "spouse": "フグ田 マスオ",
      "children": "フグ田 タラオ"
    }
  }
}
```


<details><summary>Insert文は長いので折りたたみ</summary><div>


```sql
INSERT INTO Posts (content) VALUES 
('{
  "character": {
    "name": "フグ田 サザエ",
    "age": 24,
    "profession": "主婦",
    "family": {
      "spouse": "フグ田 マスオ",
      "children": "フグ田 タラオ"
    }
  }
}'::jsonb),
('{
  "character": {
    "name": "磯野 波平",
    "age": 54,
    "profession": "無職",
    "family": {
      "spouse": "磯野 フネ",
      "children": ["フグ田 サザエ", "磯野 カツオ", "磯野 ワカメ"]
    }
  }
}'::jsonb),
('{
  "character": {
    "name": "磯野 フネ",
    "age": 50,
    "profession": "主婦",
    "family": {
      "spouse": "磯野 波平",
      "children": ["フグ田 サザエ", "磯野 カツオ", "磯野 ワカメ"]
    }
  }
}'::jsonb),
('{
  "character": {
    "name": "フグ田 マスオ",
    "age": 28,
    "profession": "サラリーマン",
    "family": {
      "spouse": "フグ田 サザエ",
      "children": "磯野 タラオ"
    }
  }
}'::jsonb),
('{
  "character": {
    "name": "磯野 カツオ",
    "age": 11,
    "profession": "小学生",
    "family": {
      "father": "磯野 波平",
      "mother": "磯野 フネ",
      "sisters": ["フグ田 サザエ", "磯野 ワカメ"]
    }
  }
}'::jsonb),
('{
  "character": {
    "name": "磯野 ワカメ",
    "age": 9,
    "profession": "小学生",
    "family": {
      "father": "磯野 波平",
      "mother": "磯野 フネ",
      "siblings": ["フグ田 サザエ", "磯野 カツオ"]
    }
  }
}'::jsonb),
('{
  "character": {
    "name": "フグ田 タラオ",
    "age": 3,
    "profession": "幼稚園児",
    "family": {
      "father": "フグ田 マスオ",
      "mother": "フグ田 サザエ"
    }
  }
}'::jsonb);

```

</div></details>


さて先ほどと同じ流れで「jsonの中で特定のフィールドのみを取得してみる」から始めてみます。

### ネストされたフィールドのみを取得してみる

ネストされていない場合は、

`(カラム名) ->> '(フィールド名)'` と書きましたね。

```sql
SELECT content ->> 'character' AS character FROM posts;
```

![スクリーンショット 2023-07-08 15.45.08.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/107345/112dcc30-026d-290f-4ab1-06e0e37833a1.png)

では、次に **「character」の中にある「name」** のみを取得してみます！
先ほどの例で言うと
```json
{
  "character": {
    "name": "フグ田 サザエ"
  }
}
```
の部分です。

このときは
`(カラム名) -> '(フィールド名)' ->> '(フィールド名)'` という表現を使いましょう！

```sql
SELECT content -> 'character' ->> 'name' AS character_name
FROM posts;
```

他にも以下のような書き方ができます。
```sql
SELECT content #> '{character, name}' AS character_name
FROM posts;
```

![スクリーンショット 2023-07-08 15.50.06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/107345/841c3d25-cd39-15c7-6bea-b1e91db35b1d.png)


## ネストされたJsonに対して検索
名前に「磯野」を含むデータを取得してみましょう。
**「character」の中にある「name」** に「磯野」を含むデータです。

`(カラム名) -> '(フィールド名)' ->> '(フィールド名)'`
表現が使えますね！

```sql
SELECT content -> 'character' ->> 'name'
FROM posts
WHERE content -> 'character' ->> 'name' LIKE '%磯野%';
```



![スクリーンショット 2023-07-09 13.19.51.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/107345/42c2701f-1f21-1a27-8add-9ec018868108.png)

これでネストされたjsonの扱い方もわかってきました!

ではもっとネストされているjsonの場合はどうなるでしょうか。


# STEP4: 多段ネストされたデータを取得したい

今までのクエリで「character」に含まれる「name」の取得まではできました。

次は、このデータからさらにネストされた **「character」に含まれる「family」に含まれる「children」** の部分を取得してみます！
```json
{
  "character": {
    "name": "フグ田 サザエ",
    "age": 24,
    "profession": "主婦",
    "family": {
      "spouse": "フグ田 マスオ",
      "children": "フグ田 タラオ"
    }
  }
}
```

ネストされていない場合は
`(カラム名) ->> '(フィールド名)'`

1段階ネストされた場合は
`(カラム名) -> '(フィールド名)' ->> '(フィールド名)'`
でしたね！

では、、、2段階ネストされた場合はどうなるでしょうか？

2段階ネストされた場合は
`(カラム名) -> '(フィールド名)' -> '(フィールド名)' ->> '(フィールド名)'`
となります！

クエリの例は以下です。
```sql
SELECT content -> 'character' -> 'family' ->> 'children' AS spouse_name
FROM posts;
```
![スクリーンショット 2023-07-08 16.01.13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/107345/6f2d73e8-4429-30b0-1249-92ec423c7164.png)

なんとなく規則性がわかってきた気がしますね！


## 多段にネストされたjsonの検索をする

ネストされていない場合は
`(カラム名) ->> '(フィールド名)'`

1段階ネストされた場合は
`(カラム名) -> '(フィールド名)' ->> '(フィールド名)'`

2段階ネストされた場合は
`(カラム名) -> '(フィールド名)' -> '(フィールド名)' ->> '(フィールド名)'` 

でしたね。

これを使って、次は検索を行います！

```json
{
"character": {
  "name": "磯野 ワカメ",
  "age": 9,
  "profession": "小学生",
  "family": {
    "father": "磯野 波平",
    "mother": "磯野 フネ",
    "siblings": ["フグ田 サザエ", "磯野 カツオ"]
  }
}
```
**「character」に含まれる「family」に含まれる「mother」** に「磯野」を含むデータを取得してみましょう！

```sql
SELECT *
FROM posts
WHERE content -> 'character' -> 'family' ->> 'mother' LIKE '%磯野%';
```

![スクリーンショット 2023-07-09 13.12.13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/107345/bb18e97c-fdb3-17eb-2261-5b5651398aa2.png)

「磯野 カツオ」と「磯野 ワカメ」が取得できました！


これでネストされたjsonの扱い方も理解できてきたと思います。

# STEP5: Jsonの中に配列がある場合は...？

## 配列の中のテキストを扱う場合
この章では以下のようなjsonを扱ってみます。
```json
{
  "name": "フグ田 サザエ",
  "age": 24,
  "profession": "主婦",
  "hobbies": ["料理", "家事", "ショッピング"]
}
```

<details><summary>Insert文は長いので折りたたみ</summary><div>

```sql
INSERT INTO Posts (content) VALUES 
('{
  "name": "フグ田 サザエ",
  "age": 24,
  "profession": "主婦",
  "hobbies": ["料理", "家事", "ショッピング"]
}'::jsonb),
('{
  "name": "磯野 波平",
  "age": 54,
  "profession": "無職",
  "hobbies": ["釣り", "新聞読む", "寝る"]
}'::jsonb),
('{
  "name": "磯野 フネ",
  "age": 50,
  "profession": "主婦",
  "hobbies": ["家事", "散歩"]
}'::jsonb),
('{
  "name": "フグ田 マスオ",
  "age": 28,
  "profession": "サラリーマン",
  "hobbies": ["釣り", "ゴルフ", "読書"]
}'::jsonb);
```

</div></details>

hobbiesに「家事」を含むデータの検索を行ってみたいと思います。

ここで、SPEP1で行ったjsonの処理関数の一つ、JSONB_ARRAY_ELEMENTS_TEXTが使えます。
JSONB_ARRAY_ELEMENTS_TEXTは、指定したJsonbの配列内の要素を「テキスト」として返します。
```sql
SELECT posts.* FROM posts, JSONB_ARRAY_ELEMENTS_TEXT(content->'hobbies') hobby
WHERE hobby = '釣り';
```
![スクリーンショット 2023-07-15 14.42.36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/107345/c7061d06-eb0a-0911-5fac-c42fd73e32f2.png)


## 配列の中のJsonを扱う場合

先ほどは、配列の中にはテキストが存在していました。
今回は、配列の中にJsonがある場合を扱います。

いよいよゴールに近づいてきました。
では、最初の章で示した以下のようなデータの配列の中にある「text」に対して検索をおこなってみます。

```json
{
  "blocks": [
    {
      "key": "ed578",
      "text": "このEditorにはDraft.jsというのを利用しています。",
      "type": "unstyled",
      "depth": "0",
      "entity_ranges": [
        ""
      ],
      "inline_style_ranges": [
        ""
      ]
    }
  ]
}
```

<details><summary>Insert文</summary><div>

```sql
INSERT INTO posts (content) VALUES 
('{
  "blocks": [
    {
      "key": "ed578",
      "text": "このEditorにはDraft.jsというのを利用しています。",
      "type": "unstyled",
      "depth": "0",
      "entity_ranges": [""],
      "inline_style_ranges": [""]
    },
    {
      "key": "fbt46",
      "text": "リンクは自動で変わります。",
      "type": "unstyled",
      "depth": "0",
      "entity_ranges": [""],
      "inline_style_ranges": [""]
    }
  ,
    {
      "key": "tgsj",
      "text": "https://example.com",
      "type": "unstyled",
      "depth": "0",
      "entity_ranges": [""],
      "inline_style_ranges": [""]
    },
    {
      "key": "4cvfg",
      "text": "太字にできます。",
      "type": "unstyled",
      "depth": "0",
      "entity_ranges": [""],
      "inline_style_ranges": [
        {
          "style": "BOLD",
          "length": "8",
          "offset": "0"
        },
        {
          "style": "UNDERLINE",
          "length": "8",
          "offset": "0"
        }
      ]
    }
  ]
}'::jsonb);
```

</div></details?


ここで、SPEP1で行ったjsonの処理関数の一つ、JSONB_ARRAY_ELEMENTSが使えます。
JSONB_ARRAY_ELEMENTSは、指定したJsonbの配列内の要素を「Json」として返します。

```sql
SELECT posts.*
FROM posts, JSONB_ARRAY_ELEMENTS(content->'blocks') block
WHERE block->>'text' LIKE '%Draft.js%';
```

![スクリーンショット 2023-07-15 14.56.01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/107345/26561b4d-047f-0b51-cfd5-91e448599685.png)

うまく動いていますね！！！

# 終わりに

これでJsonbの検索の多くができるようになったと思います！
多くの事例を出して、紹介してみたつもりです。
少なくとも記事を書く中で自分の役には立ったので、他の人の役にも立っていたら嬉しいです。
また複雑なクエリが出てきた際には追記してみます。