LoginSignup
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

この記事誰得？ 私しか得しないニッチな技術で記事投稿！
@hatsu

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

Posted at

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

ゴール

複雑なjsonbの検索ができるようになる。

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

{
  "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」が含まれているデータを取得するクエリの例です。

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

テーブル作成

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

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の処理するための関数がいくつか存在します。
ここでは、まずその中の一部を紹介してみます。

サンプルデータ

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の要素数を取得するクエリです。

SELECT id, jsonb_array_length(content->'hobbies') AS hobbies_count
FROM posts;

スクリーンショット 2023-07-15 14.08.52.png

jsonb_each関数

jsonbのkeyとvalueを返す関数です。
以下はidとそのcontentに含まれるjsonのkey, valueを取得するクエリです。

SELECT id, key, value
FROM posts, jsonb_each(content);

スクリーンショット 2023-07-15 14.11.54.png

jsonb_typeof関数

指定したキーに対応する値の型を返します。

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

json_array_elements関数

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

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

スクリーンショット 2023-07-15 14.24.08.png

json_array_elements_text

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

以下は釣りが趣味な人を返すクエリです。

SELECT posts.* FROM posts, jsonb_array_elements_text(content->'hobbies') hobby
WHERE hobby = '釣り';

スクリーンショット 2023-07-15 14.42.36.png

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

まずはの準備をします。

データinsert

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

{
  "name": "フグ田 サザエ",
  "age": 24,
  "profession": "主婦"
}

{
  "name": "磯野 波平",
  "age": 54,
  "profession": "無職"
}

{
  "name": "磯野 フネ",
  "age": 50,
  "profession": "無職"
}

{
  "name": "フグ田 マスオ",
  "age": 28,
  "profession": "サラリーマン"
}

{
  "name": "磯野 カツオ",
  "age": 11,
  "profession": "小学生"
}

{
  "name": "磯野 ワカメ",
  "age": 9,
  "profession": "小学生"
}

{
  "name": "フグ田 タラオ",
  "age": 3,
  "profession": "幼稚園児"
}

{
  "name": "中嶋",
  "age": 11,
  "profession": "小学生",
}

{
  "name": "花沢 花子",
  "age": 11,
  "profession": "小学生",
}
Insert文
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);

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

{
  "name": "フグ田 サザエ",
  "age": 24,
  "profession": "主婦"
}

このデータのうち、まずはname一覧だけ取得するクエリを書いてみます。

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

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

他には以下のような書き方もできます。

SELECT content #> '{name}' AS name FROM posts;

スクリーンショット 2023-07-08 15.09.24.png

jsonの中で特定のフィールドのみを検索対象にしてみる

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

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

スクリーンショット 2023-07-08 15.13.58.png

できました！

特定のフィールドのみで曖昧検索

では次に曖昧検索もしてみましょう！

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

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

スクリーンショット 2023-07-08 15.16.26.png

できていますね！

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

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

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

複数フィールドなので WHERE文にANDを組み合わせたクエリを作ります。

SELECT *
FROM posts
WHERE content ->> 'name' LIKE '%磯野%'
AND content ->> 'profession' = '小学生';

スクリーンショット 2023-07-08 15.22.19.png

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

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

ネストされたjsonのレコード例は以下です。

{
  "character": {
    "name": "フグ田 サザエ",
    "age": 24,
    "profession": "主婦",
    "family": {
      "spouse": "フグ田 マスオ",
      "children": "フグ田 タラオ"
    }
  }
}
Insert文は長いので折りたたみ
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);

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

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

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

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

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

スクリーンショット 2023-07-08 15.45.08.png

では、次に 「character」の中にある「name」 のみを取得してみます！
先ほどの例で言うと

{
  "character": {
    "name": "フグ田 サザエ"
  }
}

の部分です。

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

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

他にも以下のような書き方ができます。

SELECT content #> '{character, name}' AS character_name
FROM posts;

スクリーンショット 2023-07-08 15.50.06.png

ネストされたJsonに対して検索

名前に「磯野」を含むデータを取得してみましょう。
「character」の中にある「name」 に「磯野」を含むデータです。

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

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

スクリーンショット 2023-07-09 13.19.51.png

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

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

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

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

次は、このデータからさらにネストされた 「character」に含まれる「family」に含まれる「children」 の部分を取得してみます！

{
  "character": {
    "name": "フグ田 サザエ",
    "age": 24,
    "profession": "主婦",
    "family": {
      "spouse": "フグ田 マスオ",
      "children": "フグ田 タラオ"
    }
  }
}

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

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

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

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

クエリの例は以下です。

SELECT content -> 'character' -> 'family' ->> 'children' AS spouse_name
FROM posts;

スクリーンショット 2023-07-08 16.01.13.png

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

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

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

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

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

でしたね。

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

{
"character": {
  "name": "磯野 ワカメ",
  "age": 9,
  "profession": "小学生",
  "family": {
    "father": "磯野 波平",
    "mother": "磯野 フネ",
    "siblings": ["フグ田 サザエ", "磯野 カツオ"]
  }
}

「character」に含まれる「family」に含まれる「mother」 に「磯野」を含むデータを取得してみましょう！

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

スクリーンショット 2023-07-09 13.12.13.png

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

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

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

配列の中のテキストを扱う場合

この章では以下のようなjsonを扱ってみます。

{
  "name": "フグ田 サザエ",
  "age": 24,
  "profession": "主婦",
  "hobbies": ["料理", "家事", "ショッピング"]
}
Insert文は長いので折りたたみ
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);

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

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

SELECT posts.* FROM posts, JSONB_ARRAY_ELEMENTS_TEXT(content->'hobbies') hobby
WHERE hobby = '釣り';

スクリーンショット 2023-07-15 14.42.36.png

配列の中のJsonを扱う場合

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

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

{
  "blocks": [
    {
      "key": "ed578",
      "text": "このEditorにはDraft.jsというのを利用しています。",
      "type": "unstyled",
      "depth": "0",
      "entity_ranges": [
        ""
      ],
      "inline_style_ranges": [
        ""
      ]
    }
  ]
}
Insert文
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);
ここで、SPEP1で行ったjsonの処理関数の一つ、JSONB_ARRAY_ELEMENTSが使えます。
JSONB_ARRAY_ELEMENTSは、指定したJsonbの配列内の要素を「Json」として返します。 
SELECT posts.*
FROM posts, JSONB_ARRAY_ELEMENTS(content->'blocks') block
WHERE block->>'text' LIKE '%Draft.js%';

スクリーンショット 2023-07-15 14.56.01.png

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

終わりに

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

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?